<?php

/**
 * TQuery
 *
 * Responsavel por armazenar todas as querys do sistema.
 */
abstract class TQuery {

    /**
     * @uses    loginService::logar
     * @author  Richard Brandao & Vinicius Almeida
     * @since   2011-09-10
     * @param   string  $StEmail    Email digitado no formulario
     * @param   string  $StSenha    Senha digitada no formulario
     */
    public static function getSqlLogar($StEmail, $StSenha) {
        return '
            SELECT
                IDUsuario
            FROM
                '.TBL_USER.'
            WHERE
                StEmail = "' . $StEmail . '" AND StSenha = AES_ENCRYPT("' . $StSenha . '", "' . MYSQL_AESENCRYPT_KEY . '") AND EnStatus = "ATIVADO"
        ';
    }

    //##LOAD##//
    /**
     * @uses    Usuario::loadUserData
     * @author  Richard Brandao
     * @since   2011-09-19
     * @param   int  $IDUsuario  Id do usuario a ter dados carregados da tabela '.TBL_USER.'
     */
    public static function getSqlUserLoad($IDUsuario) {
        return 'SELECT
                  u.IDUsuario, u.StNome, u.StEmail, AES_DECRYPT(u.StSenha, "' . MYSQL_AESENCRYPT_KEY . '") AS StSenha, u.EnStatus, u.DtNascimento, u.StCpf, u.StRg, u.StEndereco, u.StNumero, u.StComplemento, u.StBairro, u.StCidade,
                  u.StEstado, u.StCep, u.StDDDTelefone, u.StTelefone, u.StDDDCelular, u.StCelular, u.ItRamal
                FROM
                  '.TBL_USER.' u
                WHERE
                  u.IDUsuario = "' . $IDUsuario . '"';
    }

    /**
     * @uses    Usuario::loadAdministrationData
     * @author  Richard Brandao
     * @since   2011-09-19
     * @param   int     $IDUsuario  Id do Usuario a ter dados carregados da tabela '.TBL_ADMINISTRATION.'
     */
    public static function getSqlAdmLoad($IDUsuario) {
        return '
            SELECT
                a.*
            FROM
                '.TBL_USER.' u
            LEFT JOIN
                '.TBL_ADMINISTRATION.' a
            ON
                u.IDUsuario = a.IDUsuario
            WHERE
                u.IDUsuario = "' . $IDUsuario . '"';
    }

    /**
     * @uses    Usuario::loadAllDependents
     * @author  Richard Brandao
     * @since   2011-09-20
     * @return  int     $IDusuario  ID do usuario que tera todos os dependentes carregados
     */
    public static function getSqlDependentLoad($IDUsuario) {
        return '
            SELECT
                d.*
            FROM
                '.TBL_USER.' u
            LEFT JOIN
                '.TBL_ADMINISTRATION.' a
            ON
                u.IDUsuario = a.IDUsuario
            RIGHT JOIN
                '.TBL_DEPENDENT.' d
            ON
                a.IDAdministracao = d.IDAdministracao
            WHERE
                u.IDUsuario = "' . $IDUsuario . '"';
    }

    /**
     * @uses    Funcao::getSqlPerfilLoad
     * @author  Richard Brandao
     * @since   2011-09-20
     * @param   int $IDUsuario  Id do usuario que tera o perfil carregado
     */
    public static function getSqlPerfilLoad($IDUsuario) {
        return '
            SELECT
                p.IDPerfil, p.StPerfil, p.IDFuncao
            FROM
                '.TBL_USER.' u
            LEFT JOIN
                '.TBL_PROFILE.' p
            ON
                p.IDPerfil = u.IDPerfil
            WHERE
                u.IDUsuario = "' . $IDUsuario . '"
        ';
    }

    /**
     * @uses    Perfil::loadPermissions
     * @author  Richard Brandao
     * @since   2011-09-20
     * @param   int $IDPerfil   Id do perfil que tera as permissoes carregadas
     */
    public static function getSqlPermissaoLoad($IDPerfil) {
        return '
            SELECT
                pa.IDPerfil, pa.IDAcao, a.StAcao, a.StDescricao, a.EnRestrito
            FROM
                '.TBL_PROFILE.' p
            LEFT JOIN
                '.TBL_ACTION_PROFILE.' pa
            ON
                pa.IDPerfil = p.IDPerfil
            RIGHT JOIN
                '.TBL_ACTION.' a
            ON
                a.IDAcao = pa.IDAcao
            WHERE
                p.IDPerfil = "' . $IDPerfil . '" AND a.EnRestrito = "TRUE"
        ';
    }

    public static function getSqlFerramentasHabilitadasLoad($IDUsuario) {
        return '
	    SELECT
		f.IDFerramenta, u.IDUsuario, f.StFerramenta, f.StDescricao, f.StIconName
	    FROM
		'.TBL_USER.' u
	    RIGHT JOIN
		'.TBL_ENABLED_TOOLS.' fh
	    ON
		fh.IDUsuario = u.IDUsuario
	    LEFT JOIN
		'.TBL_TOOLS.' f
	    ON
		f.IDFerramenta = fh.IDFerramenta
	    WHERE
		u.IDUsuario = "' . $IDUsuario . '"
	';
    }

    /**
     * @uses    Ferramentas::getSql
     * @author  Richard Brandao
     * @since   2011-09-28
     * @param   int $IDUsuario     ID do usuario que tera sus avisos carregados
     */
    public static function getSqlAvisosLoad() {
        return '
            SELECT
              a.*, ca.StCategoria
            FROM
              '.TBL_USER.' u
            LEFT JOIN
              '.TBL_NOTICES.' a
            ON
              a.IDUsuario = u.IDUsuario
            RIGHT JOIN
              '.TBL_NOTICE_CATEGORY.' ca
            ON
              ca.IDCategoria = a.IDCategoria
            WHERE
              DATEDIFF(a.DtInicio, now()) <= 0 AND DATEDIFF(a.DtDesativar, now()) >= 0
        ';
    }

    /**
     * @uses    Ferramentas::getSql
     * @author  Richard Brandao
     * @since   2011-09-28
     * @param   int $IDUsuario     ID do usuario que tera suas anotacoes carregadas
     */
    public static function getSqlAnotacoesLoad($IDUsuario) {
        return 'SELECT
                   a.IDAnotacao, a.StTitulo, a.StAnotacao
                FROM
                   '.TBL_USER.' u
                RIGHT JOIN
                   '.TBL_ANNOTATIONS.' a
                ON
                   a.IDUsuario = u.IDUsuario
                WHERE
                   u.IDUsuario = "' . $IDUsuario . '"';
    }

    /**
     * @uses    Usuario::loadAllPreferences, PreferenciaDao::getAllPreferences
     * @author  Richard Brandao
     * @since   2011-10-27
     * @param   int     $IDUsuario  Id do usuario que tera suas preferencias carregadas
     */
    public static function getSqlPreferencesLoad($IDUsuario) {
        return '
            SELECT
                p.ItMaximoFerramentas
            FROM
                '.TBL_USER.' u
            LEFT JOIN
                '.TBL_PREFERENCES.' p
            ON
                u.IDUsuario = p.IDUsuario
            WHERE
                u.IDUsuario = "' . $IDUsuario . '"
        ';
    }

    /**
     * @uses    Usuario::loadDepartament
     * @author  Richard Brandao
     * @since   2011-12-24
     * @param   int $IDUsuario  ID Usuario que tera seu departamento carregado
     */
    public static function getSqlDepartamentUserLoad($IDUsuario) {
        return 'SELECT
                    d.IDDepartamento, d.StDepartamento
                FROM
                    '.TBL_USER.' u
                LEFT JOIN
                    '.TBL_SECTOR_USER.' us
                ON
                    us.IDUsuario = u.IDUsuario
                LEFT JOIN
                    '.TBL_SECTOR.' s
                ON
                    s.IDSetor = us.IDSetor
                RIGHT JOIN
                    '.TBL_DEPARTAMENT.' d
                ON
                    d.IDDepartamento = s.IDDepartamento
                WHERE
                   u.IDUsuario = "' . $IDUsuario . '"';
    }

    /**
     * @uses    Funcao::loadAllProfiles
     * @param   int $IDFuncao  Id da funcao dos perfis procurados
     * @author  Richard Brandao
     * @since   2012-01-06
     */
    public static function getSqlLoadAllProfiles($IDFuncao) {
        return '
            SELECT
                p.IDPerfil, p.StPerfil
            FROM
                '.TBL_FUNCTION.' f
            LEFT JOIN
                '.TBL_PROFILE.' p
            ON
                f.IDFuncao = p.IDFuncao
            WHERE
                p.IDFuncao = "' . $IDFuncao . '"
        ';
    }

    ##26-01, Richard

    public static function getSqlPositionLoad($IDPosition) {
        return '
            SELECT
                IDFuncao, StFuncao
            FROM
                '.TBL_FUNCTION.'
            WHERE
                IDFuncao = "' . $IDPosition . '"
        ';
    }

    //##UPDATE##//
    /**
     * @uses    Usuario::updateUserData
     * @author  Richard Brandao
     * @since   2011-09-30
     * @param   int     $IDUsuario  ID do usuario que tera seus dados atualizados
     * @param   array   $ArDados    Array com os novos dados do usuario a ser atualizado
     */
    public static function getSqlUpdateUser($IDUsuario, $ArDados) {
        return '
            UPDATE
                '.TBL_USER.'
            SET
                StNome = "' . $ArDados['StNome'] . '", StEmail = "' . $ArDados['StEmail'] . '", StSenha = AES_ENCRYPT("' . $ArDados['StSenha'] . '","' . MYSQL_AESENCRYPT_KEY . '"), EnStatus = "' . $ArDados['EnStatus'] . '", DtNascimento = "' . $ArDados['DtNascimento'] . '", StCpf = "' . $ArDados['StCpf'] . '", StRg = "' . $ArDados['StRg'] . '", StEndereco = "' . $ArDados['StEndereco'] . '",
                StNumero = "' . $ArDados['StNumero'] . '", StComplemento = "' . $ArDados['StComplemento'] . '", StBairro = "' . $ArDados['StBairro'] . '", StCidade = "' . $ArDados['StCidade'] . '", StEstado = "' . $ArDados['StEstado'] . '", StCep = "' . $ArDados['StCep'] . '", StDDDTelefone = "' . $ArDados['StDDDTelefone'] . '",
                StTelefone = "' . $ArDados['StTelefone'] . '", StDDDCelular = "' . $ArDados['StDDDCelular'] . '", StCelular = "' . $ArDados['StCelular'] . '", ItRamal = "' . $ArDados['ItRamal'] . '"
            WHERE
                IDUsuario = "' . $IDUsuario . '"
        ';
    }

    /**
     * @uses    Usuario::savePreferencesData
     * @author  Richard Brandao
     * @since   2011-10-29
     * @param   int     $IDUsuario      id do usuario que tera preferencias alteradas
     * @param   array   $ArPreferences  Array com todos os novos valores das preferencias do usuario
     */
    public static function getSqlUpdatePreferences($IDUsuario, $ArPreferences) {
        return '
            UPDATE
                '.TBL_PREFERENCES.'
            SET
                ItMaximoFerramentas = "' . $ArPreferences['ItMaximoFerramentas'] . '"
            WHERE
                IDUsuario = "' . $IDUsuario . '"
        ';
    }

    /**
     * @uses    UsuarioDao::updateUser, AdministracaoDao::updateEmployee
     * @param   array $ArData     array com os novos dados do usuario
     * @param   int   $IDUsuario  Id do usuario que tera dados alterados
     * @since   2011-12-18
     */
    public static function getSqlUpdateUserData($ArData, $IDUsuario) {
        return '
            UPDATE
                    '.TBL_SECTOR_USER.' es
                RIGHT JOIN
                    '.TBL_USER.' u
                ON
                    u.IDUsuario = es.IDUsuario
                LEFT JOIN
                    '.TBL_ADMINISTRATION.' a
                ON
                    a.IDUsuario = u.IDUsuario
                SET
                    u.StNome = "' . $ArData['Pessoal']['StNome'] . '",  u.DtNascimento = "' . $ArData['Pessoal']['DtNascimento'] . '",  u.StCpf = "' . $ArData['Pessoal']['StCpf'] . '",  u.StRg = "' . $ArData['Pessoal']['StRg'] . '",  u.StEndereco = "' . $ArData['Pessoal']['StEndereco'] . '",  u.StNumero = "' . $ArData['Pessoal']['StNumero'] . '",  u.StComplemento = "' . $ArData['Pessoal']['StComplemento'] . '",  u.StBairro = "' . $ArData['Pessoal']['StBairro'] . '", u.EnStatus = "'. $ArData['Pessoal']['EnStatus'] .'",
                    u.StCidade = "' . $ArData['Pessoal']['StCidade'] . '",  u.StEstado = "' . $ArData['Pessoal']['StEstado'] . '",  u.StCep = "' . $ArData['Pessoal']['StCep'] . '",  u.StEmail = "' . $ArData['Pessoal']['StEmail'] . '",  u.ItRamal = "' . $ArData['Pessoal']['ItRamal'] . '",  u.StDDDTelefone = "' . $ArData['Pessoal']['StDDDTelefone'] . '",  u.StTelefone = "' . $ArData['Pessoal']['StTelefone'] . '",  u.StDDDCelular = "' . $ArData['Pessoal']['StDDDCelular'] . '",
                    u.StCelular = "' . $ArData['Pessoal']['StCelular'] . '",  a.FoSalario = "' . $ArData['Administrativo']['FoSalario'] . '",  a.StBanco = "' . $ArData['Administrativo']['StBanco'] . '",  a.StAgencia = "' . $ArData['Administrativo']['StAgencia'] . '",  a.StConta = "' . $ArData['Administrativo']['StConta'] . '",  a.EnStatus = "' . $ArData['Administrativo']['EnStatus'] . '",  a.DtAdmissao = "' . $ArData['Administrativo']['DtAdmissao'] . '",  a.DtDemissao = ' . ( empty($ArData['Administrativo']['DtDemissao']) ? 'NULL' : '"' . $ArData['Administrativo']['DtDemissao'] . '"' ) . ',
                    a.EnEstadoCivil = "' . $ArData['Administrativo']['EnEstadoCivil'] . '",  a.StPis = "' . $ArData['Administrativo']['StPis'] . '",  a.StCertificadoReservista = "' . $ArData['Administrativo']['StCertificadoReservista'] . '",  a.StTituloEleitor = "' . $ArData['Administrativo']['StTituloEleitor'] . '",  es.IDSetor = "' . $ArData['Funcao']['IDSetor'] . '",  u.IDPerfil = "' . $ArData['Funcao']['IDPerfil'] . '"
                WHERE
                    u.IDUsuario = "' . $IDUsuario . '"
        ';
    }

    /**
     * @uses    DependenteDao::update
     * @param   Dependente $Dependente  Objeto dependente com os novos dados do dependente
     * @author  Richard Brandao
     * @since   2011-12-18
     */
    public static function getSqlUpdateDependent($Dependente) {
        return '
            UPDATE
                '.TBL_DEPENDENT.'
            SET
                StNome = "' . $Dependente->getStNome() . '", DtNascimento = "' . $Dependente->getDtNascimento() . '", StGrauParentesco = "' . $Dependente->getStGrauParentesco() . '"
            WHERE
                IDDependente = "' . $Dependente->getIDDependente() . '"
        ';
    }

    //##FIND##//
    /**
     * @uses    FerramentasDao::getAllTools
     * @author  Richard Brandao
     * @since   2011-10-24
     */
    public static function getSqlListarFerramentas() {
        return '
            SELECT
                *
            FROM
                '.TBL_TOOLS.'
        ';
    }

    /**
     * @uses    UsuarioRepository::findAllUsers
     * @author  Richard Brandao <richardsbrandao@gmail.com>
     * @since   2011-12-06
     */
    public static function getSqlFindAllUser() {
        return '
            SELECT
                IDUsuario, StNome, StEmail, EnStatus
            FROM
                '.TBL_USER.'
            ORDER BY
                StNome ASC
        ';
    }

    /**
     * @uses    UsuarioRepository::findUserByStatus
     * @param   string  $StStatus   Status que tera os usuarios filtrados
     * @author  RIchard Brandao <richardsbrandao@gmail.com>
     * @since   2011-12-06
     */
    public static function getSqlFindUserByStatus($StStatus) {
        return '
            SELECT
                IDUsuario
            FROM
                '.TBL_USER.'
            WHERE
                EnStatus = "' . $StStatus . '"
            ORDER BY
                StNome ASC
        ';
    }

    /**
     * @uses    DepartamentoRepository::findAllDepartaments
     * @author  RIchard Brandao <richardsbrandao@gmail.com>
     * @since   2011-12-11
     */
    public static function getSqlFindAllDepartaments() {
        return '
            SELECT
                IDDepartamento, StDepartamento
            FROM
                '.TBL_DEPARTAMENT.'
            ORDER BY
                StDepartamento ASC
        ';
    }

    /**
     * @uses    Departamento::loadSectors
     * @param   int     $IDDepartamento     Id do departamento que tera seus setores capturados
     * @author  Richard Brandao <richardsbrandao@gmail.com>
     * @since   2011-12-25
     */
    public static function getSqlFindSectorByDepartament($IDDepartamento) {
        return '
            SELECT
                s.IDDepartamento, s.IDSetor, s.StSetor
            FROM
                '.TBL_DEPARTAMENT.' d
            LEFT JOIN
                '.TBL_SECTOR.' s
            ON
                d.IDDepartamento = s.IDDepartamento
            WHERE
                s.IDDepartamento = "' . $IDDepartamento . '"
        ';
    }

    /**
     * @uses    FuncaoRepository::findAllPositions
     * @author  Richard Brandao <richardsbrandao@gmail.com>
     * @since   2011-12-28
     */
    public static function getSqlFindAllPositions() {
        return '
            SELECT
                *
            FROM
                '.TBL_FUNCTION.'
        ';
    }

    /**
     * @uses    DependenteDao::loadSectors
     * @param   int $IDUsuario       Id do usuario para capturar lista do setor
     * @param   int $IDDepartamento  Id do departamento do usuario
     * @author  Richard Brandao
     * @since   2012-01-06
     */
    public static function getSqlFindUserSectorByDepartament($IDUsuario, $IDDepartamento) {
        return '
             SELECT
                s.*
             FROM
                '.TBL_USER.' u
             LEFT JOIN
                '.TBL_SECTOR_USER.' us
             ON
                u.IDUsuario = us.IDUsuario
             LEFT JOIN
                '.TBL_SECTOR.' s
             ON
                s.IDSetor = us.IDSetor
             LEFT JOIN
                '.TBL_DEPARTAMENT.' d
             ON
                d.IDDepartamento = s.IDDepartamento
             WHERE
                u.IDUsuario = "' . $IDUsuario . '" AND d.IDDepartamento = "' . $IDDepartamento . '"
        ';
    }

    //##DELETE##//
    /**
     * @uses    Ferramenta::disable, FerramentaDao::disableTool
     * @param   int     $IDUsuario      ID do usuario que tera a ferramenta desabilitada
     * @param   int     $IDFerramenta   Id da ferramenta que sera desabilitada
     * @author  Richard Brandao <richardsbrandao@gmail.com>
     * @since   2011-10-29
     */
    public static function getSqlDisableTool($IDUsuario, $IDFerramenta) {
        return '
            DELETE FROM
                '.TBL_ENABLED_TOOLS.'
            WHERE
                IDFerramenta = "' . $IDFerramenta . '" AND IDUsuario = "' . $IDUsuario . '"
        ';
    }

    /**
     * @uses    DependenteDao::delete
     * @param   int $IDDependente  Id do dependente excluido
     * @author  Richard Brandao
     * @since   2011-12-23
     */
    public static function getSqlDeleteDependent($IDDependent) {
        return '
            DELETE FROM
                '.TBL_DEPENDENT.'
            WHERE
                IDDependente = "' . $IDDependent . '"
        ';
    }

    /**
     * @uses    FerramentaDao::enableTool
     * @param   int $IDUsuario      ID do usuario que tera a ferramenta habilitada
     * @param   int $IDFerramenta   Id da ferramenta que sera habilitada
     * @author  Richard Brandao <richardsbrandao@gmail.com>
     * @since   2011-10-30
     */
    public static function getSqlEnableTool($IDUsuario, $IDFerramenta) {
        return '
            INSERT INTO
                '.TBL_ENABLED_TOOLS.'
                (IDFerramenta, IDUsuario)
            VALUES
                ("' . $IDFerramenta . '", "' . $IDUsuario . '")
        ';
    }

    /**
     * @uses    DependenteDao::save
     * @param   Dependente $Dependente  Objeto dependente com os dados do novo dependente
     * @author  Richard Brandao
     * @since   2011-12-23
     */
    public static function getSqlInsertDependent($Dependent) {
        return '
            INSERT INTO
                '.TBL_DEPENDENT.'
                (IDAdministracao, StNome, DtNascimento, StGrauParentesco)
            VALUES
                ("' . $Dependent->getIDAdministracao() . '", "' . $Dependent->getStNome() . '", "' . $Dependent->getDtNascimento() . '", "' . $Dependent->getStGrauParentesco() . '")
        ';
    }

    /**
     * @uses    AdministracaoDao::saveEmployee
     * @param   array   $ArDadosUsuario     Array com os dados do usuario vindo do formulario
     * @param   int     $IDFuncao           Id da funcao do usuario no sistema
     * @author  Richard Brandao
     * @since   2012-01-15
     */
    public static function getSqlInsertUserData($ArDadosUsuario, $IDPerfil) {
        return '
            INSERT INTO
                '.TBL_USER.'
            (IDPerfil, StNome, StEmail, StSenha, EnStatus,
                DtNascimento, StCpf, StRg, StEndereco, StNumero, StComplemento,
                StBairro, StCidade, StEstado, StCep, StDDDTelefone, StTelefone,
                StDDDCelular, StCelular, ItRamal)
            VALUES
            ("' . $IDPerfil . '", "' . $ArDadosUsuario['StNome'] . '", "' . $ArDadosUsuario['StEmail'] . '", AES_ENCRYPT("' . DEFAULT_PASS . '", "' . MYSQL_AESENCRYPT_KEY . '"), "ATIVADO",
                "' . $ArDadosUsuario['DtNascimento'] . '", "' . $ArDadosUsuario['StCpf'] . '", "' . $ArDadosUsuario['StRg'] . '", "' . $ArDadosUsuario['StEndereco'] . '", "' . $ArDadosUsuario['StNumero'] . '", "' . $ArDadosUsuario['StComplemento'] . '",
                "' . $ArDadosUsuario['StBairro'] . '", "' . $ArDadosUsuario['StCidade'] . '", "' . $ArDadosUsuario['StEstado'] . '", "' . $ArDadosUsuario['StCep'] . '", "' . $ArDadosUsuario['StDDDTelefone'] . '", "' . $ArDadosUsuario['StTelefone'] . '",
                "' . $ArDadosUsuario['StDDDCelular'] . '", "' . $ArDadosUsuario['StCelular'] . '", "' . $ArDadosUsuario['ItRamal'] . '");
        ';
    }

    /**
     * @uses
     * @param type $ArDados
     * @param type $IDUsuario
     * @since 2012-01-16
     */
    public static function getSqlInsertAdmData($ArDados, $IDUsuario) {
        return '
            INSERT INTO
                '.TBL_ADMINISTRATION.'
            (IDUsuario, EnStatus, FoSalario, StBanco, StAgencia,
                StConta, DtAdmissao, DtDemissao, EnEstadoCivil, StPis,
                StCertificadoReservista, StTituloEleitor)
            VALUES
            ("' . $IDUsuario . '", "CONTRATADO", "' . $ArDados['FoSalario'] . '", "' . $ArDados['StBanco'] . '", "' . $ArDados['StAgencia'] . '",
                "' . $ArDados['StConta'] . '", "' . $ArDados['DtAdmissao'] . '", NULL, "' . $ArDados['EnEstadoCivil'] . '", "' . $ArDados['StPis'] . '",
                "' . $ArDados['StCertificadoReservista'] . '", "' . $ArDados['StTituloEleitor'] . '")

        ';
    }

    /**
     * @uses
     * @param type $ArDados
     * @param type $IDUsuario
     * @since 2012-02-12
     */
    public static function getSqlInsertUserSector($IDSetor, $IDUsuario) {
        return '
            INSERT INTO
                '.TBL_SECTOR_USER.'
                (IDUsuario, IDSetor)
           VALUES
                ("' . $IDUsuario . '", "' . $IDSetor . '")
        ';
    }

    public static function getSqlInsertPreferences($ArPreferencias, $IDUsuario) {
        return '
            INSERT INTO
                '.TBL_PREFERENCES.'
             (IDUsuario, ItMaximoFerramentas)
             VALUES
             ("' . $IDUsuario . '", "' . $ArPreferencias['ItMaximoFerramentas'] . '")
        ';
    }

    //18-03-12
    public static function getSqlInsertCustomer($ArCustomerData, $IDUsuario) {
        return '
            INSERT INTO
                '.TBL_CUSTOMER.'
            (IDUsuario, StEmail, StSenha, EnStatus, EnTipoCliente, DtInscricao, StEndereco,
                StNumero, StComplemento, StBairro, StCidade, StEstado,
                StCep, StDddTelefone, StTelefone, StDddCelular, StCelular)
            VALUES
            ("' . $IDUsuario . '", "' . $ArCustomerData['Contact']['StEmail'] . '", "' . DEFAULT_PASS . '", "NORMAL", "' . $ArCustomerData['EnTipoCliente'] . '", now(), "' . $ArCustomerData['Contact']['StEndereco'] . '",
                "' . $ArCustomerData['Contact']['StNumero'] . '", "' . $ArCustomerData['Contact']['StComplemento'] . '", "' . $ArCustomerData['Contact']['StBairro'] . '", "' . $ArCustomerData['Contact']['StCidade'] . '", "' . $ArCustomerData['Contact']['StEstado'] . '",
                "' . $ArCustomerData['Contact']['StCep'] . '", "' . $ArCustomerData['Contact']['StDddTelefone'] . '", "' . $ArCustomerData['Contact']['StTelefone'] . '", "' . $ArCustomerData['Contact']['StDddCelular'] . '", "' . $ArCustomerData['Contact']['StCelular'] . '")
        ';
    }

    public static function getSqlInsertIndividual($ArDados, $IDCustomer) {
        $ArDados['Enterprise']['StCargo'] = !empty($ArDados['Enterprise']['StCargo']) ? $ArDados['Enterprise']['StCargo'] : '';
        return '
            INSERT INTO
                '.TBL_INDIVIDUAL_CUSTOMER.'
            (IDCliente, StNome, StCpf, StRg, DtNascimento, StCargo)
            VALUES
            ("' . $IDCustomer . '", "' . $ArDados['Individual']['StNome'] . '", "' . $ArDados['Individual']['StCpf'] . '",
                "' . $ArDados['Individual']['StRg'] . '", "' . $ArDados['Individual']['DtNascimento'] . '", "' . $ArDados['Enterprise']['StCargo'] . '")
        ';
    }

    public static function getSqlInsertEnterprise($ArCustomerData, $IDCustomer, $IDIndividual) {
        return '
            INSERT INTO
                '.TBL_ENTERPRISE_CUSTOMER.'
            (IDCliente, IDRepresentante, StRazaoSocial, StNomeFantasia, DtFundacao, StCnpj)
            VALUES
            ("' . $IDCustomer . '", "' . $IDIndividual . '", "' . $ArCustomerData['Enterprise']['StRazaoSocial'] . '", "' . $ArCustomerData['Enterprise']['StNomeFantasia'] . '",
                "' . $ArCustomerData['Enterprise']['DtFundacao'] . '", "' . $ArCustomerData['Enterprise']['StCnpj'] . '")
        ';
    }

    //21-03
    public static function getSqlFindAllSectors() {
        return '
            SELECT
                *
            FROM
                '.TBL_SECTOR.'
            ORDER BY
                StSetor ASC
        ';
    }

    //22-03
    public static function getSqlFindAllActiveCustomer() {
        return '
            SELECT
                pf.IDPessoaFisica, pf.StNome, pf.StCpf, pf.StRg, pf.DtNascimento, pf.StCargo,
                c.IDCliente, c.IDUsuario, c.StEmail, c.EnStatus, c.EnTipoCliente, c.DtInscricao, c.StEndereco, c.StNumero, c.StComplemento, c.StBairro,
                c.StCidade, c.StEstado, c.StCep, c.StDddTelefone, c.StTelefone, c.StDddCelular, c.StCelular,
                pj.IDPessoaJuridica, pj.IDRepresentante, pj.StRazaoSocial, pj.StNomeFantasia, pj.DtFundacao, pj.StCnpj,
                f.IDFinanceiro, f.EnTipoPagamento, f.EnPeriodicidade, f.ItDesconto
            FROM
                '.TBL_INDIVIDUAL_CUSTOMER.' pf
            LEFT JOIN
                '.TBL_CUSTOMER.' c
            ON
                c.IDCliente = pf.IDCliente
            LEFT JOIN
                '.TBL_ENTERPRISE_CUSTOMER.' pj
            ON
                pj.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_FINANCIAL.' f
            ON
                f.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_PLANS.' p
            ON
                f.IDPlano = p.IDPlano
            WHERE
                c.EnStatus <> "CANCELADO"
        ';
    }

    //04-04
    public static function getSqlInsertNewCalled($Chamado) {
        $IDResponsavel = !is_null($Chamado->getIDResponsavel()) ? '"' . $Chamado->getIDResponsavel() . '"' : 'NULL';
        $DtFechamento = !is_null($Chamado->getDtFechamento()) ? '"' . $Chamado->getDtFechamento() . '"' : 'NULL';
        return '
            INSERT INTO
                '.TBL_CALLED.'
            (IDSetor, IDResponsavel, IDCliente, StTitulo, DtCriacao,
                DtFechamento, EnTipoChamado, EnStatus, EnPrioridade)
             VALUES
            ("' . $Chamado->getIDSetor() . '", ' . $IDResponsavel . ', "' . $Chamado->getIDCliente() . '", "' . $Chamado->getStTitulo() . '", "' . $Chamado->getDtCriacao() . '",
                ' . $DtFechamento . ', "' . $Chamado->getEnTipoChamado() . '", "' . $Chamado->getEnStatus() . '", "' . $Chamado->getEnPrioridade() . '")
        ';
    }

    public static function getSqlInsertNewMessage($Mensagem) {
        $IDUsuario = !is_null($Mensagem->getIDUsuario()) ? '"' . $Mensagem->getIDUsuario() . '"' : 'NULL';
        return '
            INSERT INTO
                '.TBL_CALLED_HISTORY.'
            (IDChamado, IDAcaoChamado, IDUsuario, IDCliente,
                TxMensagem, EnStatus, DtResposta)
            VALUES
            ("' . $Mensagem->getIDChamado() . '","' . $Mensagem->getAcaoChamado()->getIDAcaoChamado() . '",  ' . $IDUsuario . ',"' . $Mensagem->getIDCliente() . '",
                "' . $Mensagem->getTxMensagem() . '","' . $Mensagem->getEnStatus() . '","' . $Mensagem->getDtResposta() . '")
        ';
    }

    public static function getSqlFindAllPhoneCalled() {
        return '
            SELECT
                c.*
            FROM
                '.TBL_CALLED.' c
            INNER JOIN
                '.TBL_CALLED_HISTORY.' hc
            ON
                c.IDChamado = hc.IDChamado
            WHERE
                EnTipoChamado = "TELEFONICO"
            ORDER BY
                c.DtCriacao ASC
        ';
    }

    //05-04
    public static function getSqlFindSectorById($IDSetor) {
        return '
            SELECT
                *
            FROM
                '.TBL_SECTOR.'
            WHERE
                IDSetor = "' . $IDSetor . '"
        ';
    }

    public static function getSqlFindCustomerById($IDCliente) {
        return '
            SELECT
                pf.IDPessoaFisica, pf.StNome, pf.StCpf, pf.StRg, pf.DtNascimento, pf.StCargo,
                c.IDCliente, c.IDUsuario, c.StEmail, c.EnStatus, c.EnTipoCliente, c.DtInscricao, c.StEndereco, c.StNumero, c.StComplemento, c.StBairro,
                c.StCidade, c.StEstado, c.StCep, c.StDddTelefone, c.StTelefone, c.StDddCelular, c.StCelular,
                pj.IDPessoaJuridica, pj.IDRepresentante, pj.StRazaoSocial, pj.StNomeFantasia, pj.DtFundacao, pj.StCnpj,
                f.IDFinanceiro, f.EnTipoPagamento, f.EnPeriodicidade, f.ItDesconto
            FROM
                '.TBL_INDIVIDUAL_CUSTOMER.' pf
            LEFT JOIN
                '.TBL_CUSTOMER.' c
            ON
                c.IDCliente = pf.IDCliente
            LEFT JOIN
                '.TBL_ENTERPRISE_CUSTOMER.' pj
            ON
                pj.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_FINANCIAL.' f
            ON
                f.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_PLANS.' p
            ON
                f.IDPlano = p.IDPlano
            WHERE
                c.IDCliente = "' . $IDCliente . '"
        ';
    }

    public static function getSqlFindLast20PhoneCalled() {
        return '
            SELECT
                c.*
            FROM
                '.TBL_CALLED.' c
            INNER JOIN
                '.TBL_CALLED_HISTORY.' hc
            ON
                c.IDChamado = hc.IDChamado
            WHERE
                EnTipoChamado = "TELEFONICO"
            ORDER BY
                c.DtCriacao DESC
            LIMIT
                20
        ';
    }

    //07-04
    public static function getSqlFindAllMyPhoneCalled($IDUsuario) {
        return '
            SELECT
                c.*
            FROM
                '.TBL_CALLED.' c
            INNER JOIN
                '.TBL_CALLED_HISTORY.' hc
            ON
                c.IDChamado = hc.IDChamado
            WHERE
                c.IDResponsavel = "' . $IDUsuario . '" AND
                EnTipoChamado = "TELEFONICO"
            ORDER BY
                c.DtCriacao ASC
        ';
    }

    //08-04
    public static function getSqlInsertDepartament($StDepartament) {
        return '
            INSERT INTO
                '.TBL_DEPARTAMENT.'
                (StDepartamento)
            VALUES
                ("' . $StDepartament . '")
        ';
    }

    //15-04
    public static function getSqlFindDepartamentById($IDDepartamento) {
        return '
            SELECT
                *
            FROM
                '.TBL_DEPARTAMENT.'
            WHERE
                IDDepartamento = "' . $IDDepartamento . '"
        ';
    }



    //16-04



    public static function getSqlFindUserBySector($IDSetor) {
        return '
            SELECT
                u.IDUsuario
            FROM
                '.TBL_USER.' u
            LEFT JOIN
                '.TBL_SECTOR_USER.' us
            ON
                us.IDUsuario = u.IDUsuario
            LEFT JOIN
                '.TBL_SECTOR.' s
            ON
                us.IDSetor = s.IDSetor
            WHERE
                s.IDSetor = "' . $IDSetor . '"
            ORDER BY
                s.StSetor ASC
        ';
    }

    public static function getSqlInsertSector($ArDados) {
        return '
            INSERT INTO
                '.TBL_SECTOR.'
            (IDDepartamento, StSetor)
            VALUES
            ("' . $ArDados['IDDepartamento'] . '", "' . $ArDados['StSetor'] . '")
        ';
    }

    //19-04
    public static function getSqlUpdateSector($Sector) {
        return '
            UPDATE
                '.TBL_SECTOR.'
            SET
                IDDepartamento = "' . $Sector->getIDDepartamento() . '", StSetor = "' . $Sector->getStNome() . '"
            WHERE
                IDSetor = "' . $Sector->getIDSetor() . '"
        ';
    }

    public static function getSqlUpdateDismissEmployee($IDUsuario) {
        return '
            UPDATE
                '.TBL_USER.' u
            LEFT JOIN
                '.TBL_ADMINISTRATION.' a
            ON
                u.IDUsuario = a.IDUsuario
            SET
                u.EnStatus = "DESATIVADO", a.EnStatus = "DEMITIDO", a.DtDemissao = now()
            WHERE
                u.IDUsuario = "' . $IDUsuario . '"
        ';
    }

    public static function getSqlDeleteUserSector($IDUsuario) {
        return '
            DELETE FROM
                '.TBL_SECTOR_USER.'
            WHERE
                IDUsuario = "' . $IDUsuario . '"
        ';
    }

    public static function getSqlUpdateChangeSector($IDUsuario, $IDSetor) {
        return '
            UPDATE
                '.TBL_SECTOR_USER.'
            SET
                IDSetor = "' . $IDSetor . '"
            WHERE
                IDUsuario = "' . $IDUsuario . '"
        ';
    }

    public static function getSqlDeleteSector($IDSetor) {
        return '
            DELETE FROM
                '.TBL_SECTOR.'
            WHERE
                IDSetor = "' . $IDSetor . '"
        ';
    }

    //28/05
    public static function getSqlChangeSector($IDSetor, $IDNewDepartament) {
        return '
            UPDATE
                '.TBL_SECTOR.'
            SET
                IDDepartamento = "' . $IDNewDepartament . '"
            WHERE
                IDSetor = "' . $IDSetor . '"
        ';
    }

    //01-06
    public static function getSqlDeleteDepartament($IDDepartamento) {
        return '
            DELETE FROM
                '.TBL_DEPARTAMENT.'
            WHERE
                IDDepartamento = "' . $IDDepartamento . '"
        ';
    }

    //02-06
    public static function getSqlInsertPosition($StFuncao) {
        return '
            INSERT INTO
                '.TBL_FUNCTION.'
                (StFuncao)
             VALUES
                ("' . $StFuncao . '")
        ';
    }

    public static function getSqlUpdatePosition($IDFuncao, $StFuncao) {
        return '
            UPDATE
                '.TBL_FUNCTION.'
            SET
                StFuncao = "' . $StFuncao . '"
            WHERE
                IDFuncao = "' . $IDFuncao . '"
        ';
    }

    public static function getSqlFindUserByPosition($IDFuncao) {
        return '
            SELECT
                IDUsuario
            FROM
                '.TBL_USER.' u
            LEFT JOIN
                '.TBL_PROFILE.' p
            ON
                p.IDPerfil = u.IDPerfil
            WHERE
                p.IDFuncao = "' . $IDFuncao . '"
        ';
    }

    //03-06
    public static function getSqlFindAllProfiles() {
        return '
            SELECT
                IDPerfil, IDFuncao, StPerfil
            FROM
                '.TBL_PROFILE.'
        ';
    }

    public static function getSqlInsertProfile($ArDados) {
        return '
            INSERT INTO
                '.TBL_PROFILE.'
                (IDFuncao, StPerfil)
            VALUES
                ("' . $ArDados['IDFuncao'] . '", "' . $ArDados['StPerfil'] . '")
        ';
    }

    public static function getSqlFindProfileById($IDPerfil) {
        return '
            SELECT
                IDPerfil, IDFuncao, StPerfil
            FROM
                '.TBL_PROFILE.'
            WHERE
                IDPerfil = "' . $IDPerfil . '"
        ';
    }

    public static function getSqlUpdateProfile($ArDados) {
        return '
            UPDATE
                '.TBL_PROFILE.'
            SET
                IDFuncao = "' . $ArDados['IDFuncao'] . '", StPerfil = "' . $ArDados['StPerfil'] . '"
            WHERE
                IDPerfil = "' . $ArDados['IDPerfil'] . '"
        ';
    }

    //07-06
    public static function getSqlFindAllActions() {
        return '
            SELECT
                a.IDAcao, a.StAcao, a.StDescricao, a.EnRestrito, p.IDPerfil
            FROM
                '.TBL_ACTION.' a
            LEFT JOIN
                '.TBL_ACTION_PROFILE.' pa
            ON
                pa.IDAcao = a.IDAcao
            LEFT JOIN
                '.TBL_PROFILE.' p
            ON
                p.IDPerfil = pa.IDPerfil
            GROUP BY
                a.IDAcao
        ';
    }

    public static function getSqlInsertActionToProfile($IDAction, $IDPerfil) {
        return '
            INSERT INTO
                '.TBL_ACTION_PROFILE.'
                (IDAcao, IDPerfil)
            VALUES
                ("' . $IDAction . '", "' . $IDPerfil . '")
        ';
    }

    public static function getSqlFindActionsByProfile($IDPerfil) {
        return '
            SELECT
                a.IDAcao, a.StAcao, a.StDescricao, a.EnRestrito
            FROM
                '.TBL_ACTION.' a
            LEFT JOIN
                '.TBL_ACTION_PROFILE.' pa
            ON
                a.IDAcao = pa.IDAcao
            WHERE
                pa.IDPerfil = "' . $IDPerfil . '"
        ';
    }

    public static function getSqlDeleteActionToProfile($IDAction, $IDPerfil) {
        return '
            DELETE FROM
                '.TBL_ACTION_PROFILE.'
            WHERE
                IDAcao = "' . $IDAction . '" AND IDPerfil = "' . $IDPerfil . '"
        ';
    }

    public static function getSqlFindCalledById($IDChamado) {
        return '
            SELECT
                IDChamado, IDSetor, IDResponsavel, IDCliente, StTitulo,
                DtCriacao, DtFechamento, EnStatus, EnPrioridade, EnTipoChamado
            FROM
                '.TBL_CALLED.'
            WHERE
                IDChamado = "' . $IDChamado . '"
        ';
    }

    public static function getSqlAdvancedSearchCalled($ArParameters) {
        $StSql = '
            SELECT
                Chamado.*
            FROM
                '.TBL_CALLED.' Chamado
            LEFT JOIN
                (
                    SELECT
                        c.IDCliente, pf.StNome, pj.StRazaoSocial
                    FROM
                        '.TBL_INDIVIDUAL_CUSTOMER.' pf
                    LEFT JOIN
                         '.TBL_CUSTOMER.' c
                    ON
                         c.IDCliente = pf.IDCliente
                    LEFT JOIN
                         '.TBL_ENTERPRISE_CUSTOMER.' pj
                    ON
                         pj.IDCliente = c.IDCliente
                ) as Cliente
            ON
                Cliente.IDCliente = Chamado.IDCliente
            LEFT JOIN
                '.TBL_USER.' Usuario
            ON
                Usuario.IDUsuario = Chamado.IDResponsavel
         ';
        $ArFilters = array();
        if (isset($ArParameters['BoPrioridade'])) {
            $ArFilters[] = 'Chamado.EnPrioridade = "' . $ArParameters['BoPrioridade'] . '"';
        }
        if (isset($ArParameters['StTitulo'])) {
            $ArFilters[] = 'Chamado.StTitulo LIKE "%' . $ArParameters['StTitulo'] . '%"';
        }
        if (isset($ArParameters['IDSetor'])) {
            $ArFilters[] = 'Chamado.IDSetor = "' . $ArParameters['IDSetor'] . '"';
        }
        if (isset($ArParameters['StResponsavel'])) {
            $ArFilters[] = 'Usuario.StNome LIKE "%' . $ArParameters['StResponsavel'] . '%"';
        }
        if (isset($ArParameters['EnTipo'])) {
            $ArFilters[] = 'Chamado.EnTipoChamado = "' . $ArParameters['EnTipo'] . '"';
        }
        if (isset($ArParameters['EnStatus'])) {
            $ArFilters[] = 'Chamado.EnStatus = "' . $ArParameters['EnStatus'] . '"';
        }
        if (isset($ArParameters['StCliente'])) {
            $ArFilters[] = '(Cliente.StNome LIKE "%' . $ArParameters['StCliente'] . '%" OR Cliente.StRazaoSocial LIKE "%' . $ArParameters['StCliente'] . '%")';
        }
        if (isset($ArParameters['DtCriacao'])) {
            $ArFilters[] = 'Chamado.DtCriacao LIKE "' . $ArParameters['DtCriacao'] . '%"';
        }
        if (isset($ArParameters['DtFechamento'])) {
            $ArFilters[] = 'Chamado.DtFechamento LIKE "' . $ArParameters['DtFechamento'] . '%"';
        }
        if (count($ArFilters) > 0) {
            $StSql .= ' WHERE ' . implode(" {$ArParameters['FilterType']} ", $ArFilters);
        }
        $StSql .= ' ORDER BY Chamado.EnPrioridade ASC, Chamado.IDSetor ASC';
        return $StSql;
    }

    //junho
    public static function getSqlListarCidades() {
        return '
            SELECT
                StCidade
            FROM
                '.TBL_CUSTOMER.'
            GROUP BY
                StCidade
            ORDER BY
                StCidade ASC
        ';
    }

    public static function getSqlListarPlanos() {
        return '
            SELECT
                *
            FROM
                '.TBL_PLANS.'
        ';
    }

    public static function getSqlFindPlanByFinancialId($IDFinanceiro) {
        return '
            SELECT
                p.*
            FROM
                '.TBL_FINANCIAL.' f
            RIGHT JOIN
                '.TBL_PLANS.' p
            ON
                p.IDPlano = f.IDPlano
            WHERE
                f.IDFinanceiro = "' . $IDFinanceiro . '"
        ';
    }

    public static function getSqlFindLancamentosByFinancialId($param0) {

    }

    public static function getSqlFindCustomerByDocument($ArDados) {
        $StSql = '
            SELECT
                pf.IDPessoaFisica, pf.StNome, pf.StCpf, pf.StRg, pf.DtNascimento, pf.StCargo,
                c.IDCliente, c.IDUsuario, c.StEmail, c.EnStatus, c.EnTipoCliente, c.DtInscricao, c.StEndereco, c.StNumero, c.StComplemento, c.StBairro,
                c.StCidade, c.StEstado, c.StCep, c.StDddTelefone, c.StTelefone, c.StDddCelular, c.StCelular,
                pj.IDPessoaJuridica, pj.IDRepresentante, pj.StRazaoSocial, pj.StNomeFantasia, pj.DtFundacao, pj.StCnpj,
                f.IDFinanceiro, f.EnTipoPagamento, f.EnPeriodicidade, f.ItDesconto
            FROM
                '.TBL_INDIVIDUAL_CUSTOMER.' pf
            LEFT JOIN
                '.TBL_CUSTOMER.' c
            ON
                c.IDCliente = pf.IDCliente
            LEFT JOIN
                '.TBL_ENTERPRISE_CUSTOMER.' pj
            ON
                pj.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_FINANCIAL.' f
            ON
                f.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_PLANS.' p
            ON
                f.IDPlano = p.IDPlano
            WHERE
        ';
        $ArFiltros = array();
        if (isset($ArDados['StCnpj'])) {
            $ArFiltros[] = 'pj.StCnpj = "' . $ArDados['StCnpj'] . '"';
        }
        if (isset($ArDados['StCpf'])) {
            $ArFiltros[] = 'pf.StCpf = "' . $ArDados['StCpf'] . '"';
        }
        return $StSql . implode(' OR ', $ArFiltros);
    }

    public static function getSqlAdvancedSearchCustomer($ArParameters) {
        $StSql = '
            SELECT
                pf.IDPessoaFisica, pf.StNome, pf.StCpf, pf.StRg, pf.DtNascimento, pf.StCargo,
                c.IDCliente, c.IDUsuario, c.StEmail, c.EnStatus, c.EnTipoCliente, c.DtInscricao, c.StEndereco, c.StNumero, c.StComplemento, c.StBairro,
                c.StCidade, c.StEstado, c.StCep, c.StDddTelefone, c.StTelefone, c.StDddCelular, c.StCelular,
                pj.IDPessoaJuridica, pj.IDRepresentante, pj.StRazaoSocial, pj.StNomeFantasia, pj.DtFundacao, pj.StCnpj,
                f.IDFinanceiro, f.EnTipoPagamento, f.EnPeriodicidade, f.ItDesconto
            FROM
                '.TBL_INDIVIDUAL_CUSTOMER.' pf
            LEFT JOIN
                '.TBL_CUSTOMER.' c
            ON
                c.IDCliente = pf.IDCliente
            LEFT JOIN
                '.TBL_ENTERPRISE_CUSTOMER.' pj
            ON
                pj.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_FINANCIAL.' f
            ON
                f.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_PLANS.' p
            ON
                f.IDPlano = p.IDPlano
            WHERE
        ';
        $ArFilters = array();
        if (isset($ArParameters['StNome'])) {
            $ArFilters[] = '(pf.StNome LIKE "%' . $ArParameters['StNome'] . '%" OR pj.StRazaoSocial LIKE "%' . $ArParameters['StNome'] . '%" OR pj.StNomeFantasia LIKE "%' . $ArParameters['StNome'] . '%")';
        }
        if (isset($ArParameters['StEmail'])) {
            $ArFilters[] = 'c.StEmail LIKE "%' . $ArParameters['StEmail'] . '%"';
        }
        if (isset($ArParameters['EnStatus'])) {
            $ArFilters[] = 'c.EnStatus = "' . $ArParameters['EnStatus'] . '"';
        }
        if (isset($ArParameters['StCidade'])) {
            $ArFilters[] = 'c.StCidade = "' . $ArParameters['StCidade'] . '"';
        }
        if (isset($ArParameters['IDPlano'])) {
            $ArFilters[] = 'p.IDPlano = "' . $ArParameters['IDPlano'] . '"';
        }
        $StSql .= implode(" {$ArParameters['FilterType']} ", $ArFilters);
        return $StSql;
    }

    public static function getSqlInsertFinancial($IDCliente, $ArDados) {
        return '
            INSERT INTO
                '.TBL_FINANCIAL.'
                (IDCliente, IDPlano, EnTipoPagamento, EnPeriodicidade, ItDesconto)
            VALUES
                ("' . $IDCliente . '",  "' . $ArDados['Financial']['IDPlano'] . '",  "' . $ArDados['Financial']['EnTipoPagamento'] . '",  "' . $ArDados['Financial']['EnPeriodicidade'] . '",  "' . $ArDados['Financial']['ItDesconto'] . '")
        ';
    }

    public static function getSqlFindExternalCalledBySector($IDSetor) {
        return '
            SELECT
                IDChamado, IDSetor, IDResponsavel, IDCliente, StTitulo,
                DtCriacao, DtFechamento, EnTipoChamado, EnStatus, EnPrioridade
            FROM
                '.TBL_CALLED.'
            WHERE
                EnTipoChamado = "EXTERNO" AND EnStatus = "ATENDENTE" AND IDSetor = "' . $IDSetor . '"
            ORDER BY
                EnPrioridade ASC, DtCriacao ASC
        ';
    }

    public static function getSqlFindAllMessagesByCalledId($IDChamado) {
        return '
            SELECT
                ac.StAcao, ac.IDAcaoChamado, hc.*
            FROM
                '.TBL_CALLED_HISTORY.' hc
            LEFT JOIN
                '.TBL_CALLED_ACTIONS.' ac
            ON
                hc.IDAcaoChamado = ac.IDAcaoChamado
            WHERE
                IDChamado = "' . $IDChamado . '" AND hc.EnStatus <> "BLOQUEADO"
        ';
    }

    public static function getSqlFindPhoneCalledBySector($IDSetor) {
        return '
            SELECT
                IDChamado, IDSetor, IDResponsavel, IDCliente, StTitulo,
                DtCriacao, DtFechamento, EnTipoChamado, EnStatus, EnPrioridade
            FROM
                '.TBL_CALLED.'
            WHERE
                EnTipoChamado = "TELEFONICO" AND IDSetor = "' . $IDSetor . '"
            ORDER BY
                DtCriacao ASC
        ';
    }

    public static function getSqlFindAllMyExternalCalled($IDUsuario) {
        return '
            SELECT
                IDChamado, IDSetor, IDResponsavel, IDCliente, StTitulo,
                DtCriacao, DtFechamento, EnTipoChamado, EnStatus, EnPrioridade
            FROM
                '.TBL_CALLED.'
            WHERE
                EnTipoChamado = "EXTERNO" AND IDResponsavel = "' . $IDUsuario . '" AND
                EnStatus = "ATENDENTE"
            ORDER BY
                EnPrioridade ASC, DtCriacao ASC
        ';
    }

    public static function getSqlFindPriorityExternalCalled($IDSetor) {
        return '
            SELECT
                IDChamado, IDSetor, IDResponsavel, IDCliente, StTitulo,
                DtCriacao, DtFechamento, EnTipoChamado, EnStatus, EnPrioridade
            FROM
                '.TBL_CALLED.'
            WHERE
                EnTipoChamado = "EXTERNO" AND IDSetor = "' . $IDSetor . '" AND
                EnStatus = "ATENDENTE" AND EnPrioridade = "TRUE"
        ';
    }

    public static function getSqlUpdateCalledByFilter($ArFilter) {
        $StSql = '
            UPDATE
                '.TBL_CALLED.'
            SET
                %s
            WHERE
                IDChamado = "' . $ArFilter['IDChamado'] . '"
        ';
        $ArUpdate = array();
        if (isset($ArFilter['EnPrioridade'])) {
            $ArUpdate[] = 'EnPrioridade = "' . $ArFilter['EnPrioridade'] . '"';
        }
        if (isset($ArFilter['DtFechamento'])) {
            $ArUpdate[] = 'DtFechamento = "' . $ArFilter['DtFechamento'] . '"';
        }
        if (isset($ArFilter['EnStatus'])) {
            $ArUpdate[] = 'EnStatus = "' . $ArFilter['EnStatus'] . '"';
        }
        if (isset($ArFilter['IDNewSector'])) {
            $ArUpdate[] = 'IDSetor = "' . $ArFilter['IDNewSector'] . '"';
        }
        if (isset($ArFilter['IDResponsavel'])) {
            $ArUpdate[] = 'IDResponsavel = "' . $ArFilter['IDResponsavel'] . '"';
        }
        return sprintf($StSql, implode(', ', $ArUpdate));
    }

    public static function getSqlReleaseMessageInHistory($Mensagem) {
        $IDUsuario = $Mensagem->isUserMessage() ? '"' . $Mensagem->getIDUsuario() . '"' : 'NULL';
        $IDCliente = $Mensagem->isCustomerMessage() ? '"' . $Mensagem->getIDCliente() . '"' : 'NULL';
        return '
            INSERT INTO
                '.TBL_CALLED_HISTORY.'
                (IDChamado, IDAcaoChamado, IDUsuario,
                 IDCliente, TxMensagem, EnStatus, DtResposta)
            VALUES
                ("' . $Mensagem->getIDChamado() . '", "' . $Mensagem->getAcaoChamado()->getIDAcaoChamado() . '", ' . $IDUsuario . ',
                 ' . $IDCliente . ', "' . $Mensagem->getTxMensagem() . '", "' . $Mensagem->getEnStatus() . '", now())
        ';
    }

    public static function getSqlFindAllCalledActions() {
        return '
            SELECT
                IDAcaoChamado, StAcao
            FROM
                '.TBL_CALLED_ACTIONS.'
        ';
    }

    public static function getSqlTotalSalarialSheet() {
        return '
            SELECT
                SUM(a.FoSalario) AS TotalSalarialSheet
            FROM
                '.TBL_USER.' u
            LEFT JOIN
                '.TBL_ADMINISTRATION.' a
            ON
                a.IDUsuario = u.IDUsuario
            WHERE
                u.EnStatus = "ATIVADO"
        ';
    }

    public static function getSqlNumberOfEmployees() {
        return '
            SELECT
                COUNT(IDUsuario) AS NumberOfEmployees
            FROM
                '.TBL_USER.'
            WHERE
                EnStatus = "ATIVADO"
        ';
    }

    public static function getSqlTotalSalarialSheetByPosition($Position) {
        return '
            SELECT
                SUM(a.FoSalario) AS TotalSalarialSheet
            FROM
                '.TBL_PROFILE.' p
            RIGHT JOIN
                '.TBL_USER.' u
            ON
                p.IDPerfil = u.IDPerfil
            LEFT JOIN
                '.TBL_ADMINISTRATION.' a
            ON
                a.IDUsuario = u.IDUsuario
            WHERE
                p.IDFuncao = "' . $Position->getIDFuncao() . '" AND a.EnStatus = "CONTRATADO"
        ';
    }

    public static function getSqlTotalSalarialSheetByDepartament($Departament) {
        return '
            SELECT
                SUM(a.FoSalario) AS TotalSalarialSheet
            FROM
                '.TBL_DEPARTAMENT.' d
            LEFT JOIN
                '.TBL_SECTOR.' s
            ON
                s.IDDepartamento = d.IDDepartamento
            LEFT JOIN
                '.TBL_SECTOR_USER.' us
            ON
                us.IDSetor = s.IDSetor
            LEFT JOIN
                '.TBL_USER.' u
            ON
                us.IDUsuario = u.IDUsuario
            LEFT JOIN
                '.TBL_ADMINISTRATION.' a
            ON
                a.IDUsuario = u.IDUsuario
            WHERE
                d.IDDepartamento = "' . $Departament->getIDDepartamento() . '" AND a.EnStatus = "CONTRATADO"
        ';
    }

    public static function getSqlTotalSalarialSheetBySector($Sector) {
        return '
            SELECT
                SUM(a.FoSalario) AS TotalSalarialSheet
            FROM
                '.TBL_SECTOR.' s
            LEFT JOIN
                '.TBL_SECTOR_USER.' us
            ON
                us.IDSetor = s.IDSetor
            LEFT JOIN
                '.TBL_USER.' u
            ON
                us.IDUsuario = u.IDUsuario
            LEFT JOIN
                '.TBL_ADMINISTRATION.' a
            ON
                a.IDUsuario = u.IDUsuario
            WHERE
                s.IDSetor = "' . $Sector->getIDSetor() . '" AND a.EnStatus = "CONTRATADO"
        ';
    }

    public static function getSqlLastRegisteredEmployees() {
        return '
            SELECT
                u.IDUsuario
            FROM
                '.TBL_USER.' u
            LEFT JOIN
                '.TBL_ADMINISTRATION.' a
            ON
                a.IDUsuario = u.IDUsuario
            ORDER BY
                a.DtAdmissao DESC
            LIMIT
                5
        ';
    }

    public static function getSqlLastFiredEmployees() {
        return '
            SELECT
                u.IDUsuario
            FROM
                '.TBL_USER.' u
            LEFT JOIN
                '.TBL_ADMINISTRATION.' a
            ON
                a.IDUsuario = u.IDUsuario
            WHERE
                a.EnStatus =  "DEMITIDO" AND u.EnStatus =  "DESATIVADO"
            ORDER BY
                a.DtDemissao DESC
            LIMIT
                5
        ';
    }

    public static function getSqlFindCalledByCustomer($IDCliente) {
        return '
            SELECT
                IDChamado, IDSetor, IDResponsavel, IDCliente, StTitulo,
                DtCriacao, DtFechamento, EnTipoChamado, EnStatus, EnPrioridade
            FROM
                '.TBL_CALLED.'
            WHERE
                IDCliente = "' . $IDCliente . '" AND EnTipoChamado <> "INTERNO"
        ';
    }

    public static function getSqlFindCommunicationsByCustomer($IDCliente) {
        return '
            SELECT
                co.*
            FROM
                '.TBL_CUSTOMER.' cl
            RIGHT JOIN
                '.TBL_ANNONCEMENT.' co
            ON
                co.IDCliente = cl.IDCliente
            WHERE
                cl.IDCliente = "' . $IDCliente . '"
        ';
    }

    public static function getSqlFindSituationHistoryByCustomer($IDCliente) {
        return '
            SELECT
                hs.*
            FROM
                '.TBL_CUSTOMER.' c
            LEFT JOIN
                '.TBL_FINANCIAL.' f
            ON
                c.IDCliente = f.IDCliente
            RIGHT JOIN
                '.TBL_SITUATION_HISTORY.' hs
            ON
                hs.IDFinanceiro = f.IDFinanceiro
            WHERE
                c.IDCliente = "' . $IDCliente . '"
        ';
    }

    public static function getSqlFindAllInternalCalled() {
        return '
            SELECT
                IDChamado, IDSetor, IDResponsavel, IDCliente, StTitulo,
                DtCriacao, DtFechamento, EnTipoChamado, EnStatus, EnPrioridade
            FROM
                '.TBL_CALLED.'
            WHERE
                EnTipoChamado = "INTERNO" AND EnStatus = "ATENDENTE"
            ORDER BY
                EnPrioridade ASC, DtCriacao ASC
        ';
    }

    public static function getSqlFindInternalCalledBySector($IDSetor) {
        return '
            SELECT
                IDChamado, IDSetor, IDResponsavel, IDCliente, StTitulo,
                DtCriacao, DtFechamento, EnTipoChamado, EnStatus, EnPrioridade
            FROM
                '.TBL_CALLED.'
            WHERE
                EnTipoChamado = "INTERNO" AND EnStatus = "ATENDENTE" AND IDSetor = "' . $IDSetor . '"
            ORDER BY
                EnPrioridade ASC, DtCriacao ASC
        ';
    }

    public static function getSqlFindInternalCalledWaitingCustomerByUser($IDUsuario) {
        return '
            SELECT
                IDChamado, IDSetor, IDResponsavel, IDCliente, StTitulo,
                DtCriacao, DtFechamento, EnTipoChamado, EnStatus, EnPrioridade
            FROM
                '.TBL_CALLED.'
            WHERE
                EnTipoChamado = "INTERNO" AND EnStatus = "CLIENTE" AND IDResponsavel= "' . $IDUsuario . '"
            ORDER BY
                EnPrioridade ASC, DtCriacao ASC
        ';
    }

    public static function getSqlFindExternalCalledWaitingCustomerByUser($IDUsuario) {
        return '
            SELECT
                IDChamado, IDSetor, IDResponsavel, IDCliente, StTitulo,
                DtCriacao, DtFechamento, EnTipoChamado, EnStatus, EnPrioridade
            FROM
                '.TBL_CALLED.'
            WHERE
                EnTipoChamado = "EXTERNO" AND EnStatus = "CLIENTE" AND IDResponsavel= "' . $IDUsuario . '"
            ORDER BY
                EnPrioridade ASC, DtCriacao ASC
        ';
    }

    public static function getSqlTotalExternalCalledByUser($IDUsuario, $ArDates) {
        return '
            SELECT
                    COUNT(hc.IDHistoricoChamado) AS NumberExternalCalled
            FROM
                    '.TBL_CALLED.' c
            LEFT JOIN
                    '.TBL_CALLED_HISTORY.' hc
            ON
                    c.IDChamado=hc.IDchamado
            WHERE
                    hc.IDUsuario="' . $IDUsuario . '" AND  hc.IDAcaoChamado = "' . AcaoChamado::RESPOSTA . '"
                    AND c.EnTipoChamado = "EXTERNO" AND hc.EnStatus = "LIBERADO"
         ' . TQuery::formatDateFilter('hc.DtResposta', $ArDates);
    }

    public static function getSqlTotalInternalCalledByUser($IDUsuario, $ArDates) {
        return '
            SELECT
                    COUNT(hc.IDHistoricoChamado) AS NumberInternalCalled
            FROM
                    '.TBL_CALLED.' c
            LEFT JOIN
                    '.TBL_CALLED_HISTORY.' hc
            ON
                    c.IDChamado=hc.IDchamado
            WHERE
                    hc.IDusuario="' . $IDUsuario . '" AND  hc.IDAcaoChamado = "' . AcaoChamado::RESPOSTA . '"
                    AND c.EnTipoChamado = "INTERNO"
        ' . TQuery::formatDateFilter('hc.DtResposta', $ArDates);
    }

    public static function getSqlTotalPhoneCalledByUser($IDUsuario, $ArDates) {
        return '
          SELECT
                COUNT(hc.IDHistoricoChamado) AS NumberPhoneCalled
          FROM
                '.TBL_CALLED.' c
          LEFT JOIN
                '.TBL_CALLED_HISTORY.' hc
          ON
                c.IDChamado=hc.IDchamado
          WHERE
                hc.IDusuario="' . $IDUsuario . '" AND  hc.IDAcaoChamado = "' . AcaoChamado::TELEFONICO . '"
                AND c.EnTipoChamado = "TELEFONICO"
        ' . TQuery::formatDateFilter('hc.DtResposta', $ArDates);
    }

    public static function getSqlReportTotalCustomerByUser($IDUsuario, $ArDates) {
        return'
            SELECT
                 COUNT(IDCliente) AS ReportCustomer
            FROM
                 '.TBL_CUSTOMER.'
            WHERE
                 IDUsuario="' . $IDUsuario . '"
        ' . TQuery::formatDateFilter('DtInscricao', $ArDates);
    }

    public static function getSqlTotalAnswersInternalCalledByUser($IDUsuario, $ArDates) {
        return '
            SELECT
                COUNT(c.IDChamado) AS NumberInternalCalled
            FROM
                '.TBL_CALLED.' c
            LEFT JOIN
                '.TBL_CALLED_HISTORY.' hc
            ON
                hc.IDChamado = c.IDChamado
            WHERE
                c.EnTipoChamado = "INTERNO" AND hc.IDUsuario = "' . $IDUsuario . '" AND hc.IDAcaoChamado = "' . AcaoChamado::RESPOSTA . '"
                AND hc.EnStatus = "LIBERADO"
        ' . TQuery::formatDateFilter('hc.DtResposta', $ArDates);
    }

    public static function getSqlTotalExternalAnswersByUser($IDUsuario, $ArDates) {
        return '
            SELECT
                COUNT(c.IDChamado) AS NumberExternalCalled
            FROM
                '.TBL_CALLED.' c
            LEFT JOIN
                '.TBL_CALLED_HISTORY.' hc
            ON
                hc.IDChamado = c.IDChamado
            WHERE
                c.EnTipoChamado = "EXTERNO" AND hc.IDUsuario = "' . $IDUsuario . '" AND hc.IDAcaoChamado = "' . AcaoChamado::RESPOSTA . '"
                AND hc.EnStatus = "LIBERADO"
         ' . TQuery::formatDateFilter('hc.DtResposta', $ArDates);
    }

    public static function getSqlTotalNotClosedCalledByUser($IDUsuario, $ArDates) {
        return '
            SELECT
                COUNT(c.IDChamado) AS NumberNotClosedCalled
            FROM
                '.TBL_CALLED.' c
            LEFT JOIN
                '.TBL_CALLED_HISTORY.' hc
            ON
                hc.IDChamado = c.IDChamado and hc.IDCliente = c.IDCliente
            WHERE
                c.IDResponsavel = "'.$IDUsuario.'" and c.EnTipoChamado = "EXTERNO" and c.EnStatus <> "FECHADO"
        ' . TQuery::formatDateFilter('hc.DtResposta', $ArDates);
    }

    public static function getSqlTotal1AnswerClosedCalledByUser($IDUsuario, $ArDates) {
        return '
            SELECT
                COUNT(NumberOfAnswers) AS NumberOf1AnswerClosedCalled
            FROM
                (
                    SELECT
                        COUNT(hc.IDChamado) AS NumberOfAnswers
                    FROM
                        '.TBL_CALLED.' c
                    LEFT JOIN
                        '.TBL_CALLED_HISTORY.' hc
                    ON
                        c.IDChamado = hc.IDChamado
                    WHERE
                        hc.IDUsuario = "' . $IDUsuario . '" AND c.EnStatus = "FECHADO" AND
                        c.EnTipoChamado <> "TELEFONICO"  and hc.IDAcaoChamado = "' . AcaoChamado::RESPOSTA . '"
                         ' . TQuery::formatDateFilter('hc.DtResposta', $ArDates) . '
                    GROUP BY
                        hc.IDChamado
                ) SubQuery
            WHERE
                NumberOfAnswers = 1
        ';
    }

    public static function getSqlFindAllEvaluationsByUser($IDUsuario, $ArDates) {
        return '
            SELECT
                ac.*
            FROM
                '.TBL_CALLED.' c
            RIGHT JOIN
                '.TBL_CALLED_EVALUATION.' ac
            ON
                ac.IDChamado = c.IDChamado
            WHERE
                c.IDResponsavel = "' . $IDUsuario . '"
        ' . TQuery::formatDateFilter('c.DtFechamento', $ArDates);
    }

    private static function formatDateFilter($StField, $ArDates) {
        $ArFilterDate = array();
        if (!empty($ArDates['Start'])) {
            $ArFilterDate['Start'] = $StField . ' > "' . dateBRtoBD($ArDates['Start']) . '"';
        }
        if (!empty($ArDates['End'])) {
            $ArFilterDate['End'] = $StField . ' < "' . dateBRtoBD($ArDates['End']) . '"';
        }
        return count($ArFilterDate) > 0 ? ' AND ' . implode(' AND ', $ArFilterDate) : '';
    }

    public static function getSqlTotalInternalCalledBySector($IDSetor, $ArPeriod) {
        return '
            SELECT
                    COUNT(hc.IDHistoricoChamado) AS NumberInternalCalled
            FROM
                    '.TBL_CALLED.' c
            LEFT JOIN
                    '.TBL_CALLED_HISTORY.' hc
            ON
                    c.IDChamado=hc.IDchamado
            WHERE
                    c.IDSetor ="' . $IDSetor . '" AND  hc.IDAcaoChamado = "' . AcaoChamado::RESPOSTA . '"
                    AND c.EnTipoChamado = "INTERNO" AND hc.IDUsuario IS NOT NULL
        ' . TQuery::formatDateFilter('hc.DtResposta', $ArPeriod);
    }

    public static function getSqlTotalExternalCalledBySector($IDSetor, $ArPeriod) {
        return '
            SELECT
                    COUNT(hc.IDHistoricoChamado) AS NumberExternalCalled
            FROM
                    '.TBL_CALLED.' c
            LEFT JOIN
                    '.TBL_CALLED_HISTORY.' hc
            ON
                    c.IDChamado=hc.IDchamado
            WHERE
                    c.IDSetor ="' . $IDSetor . '" AND  hc.IDAcaoChamado = "' . AcaoChamado::RESPOSTA . '"
                    AND c.EnTipoChamado = "EXTERNO" AND hc.IDUsuario IS NOT NULL
        ' . TQuery::formatDateFilter('hc.DtResposta', $ArPeriod);
    }

    public static function getSqlTotalPhoneCalledBySector($IDSetor, $ArPeriod) {
        return '
          SELECT
                COUNT(hc.IDHistoricoChamado) AS NumberPhoneCalled
          FROM
                '.TBL_CALLED.' c
          LEFT JOIN
                '.TBL_CALLED_HISTORY.' hc
          ON
                c.IDChamado=hc.IDchamado
          WHERE
                c.IDSetor = "' . $IDSetor . '" AND  hc.IDAcaoChamado = "' . AcaoChamado::TELEFONICO . '"
                AND c.EnTipoChamado = "TELEFONICO" AND hc.IDUsuario IS NOT NULL
        ' . TQuery::formatDateFilter('hc.DtResposta', $ArPeriod);
    }

    public static function getSqlTotalNotClosedCalledBySector($IDSetor, $ArPeriod) {
        return '
            SELECT
                COUNT(c.IDChamado) AS NumberNotClosedCalled
            FROM
                '.TBL_CALLED.' c
            LEFT JOIN
                '.TBL_CALLED_HISTORY.' hc
            ON
                hc.IDChamado = c.IDChamado and hc.IDCliente = c.IDCliente
            WHERE
                c.IDSetor = "'.$IDSetor.'" and c.EnTipoChamado = "EXTERNO" and c.EnStatus <> "FECHADO"
        ' . TQuery::formatDateFilter('hc.DtResposta', $ArPeriod);
    }

    public static function getSqlTotal1AnswerClosedCalledBySector($IDSetor, $ArPeriod) {
        return '
            SELECT
                COUNT(NumberOfAnswers) AS NumberOf1AnswerClosedCalled
            FROM
                (
                    SELECT
                        COUNT(hc.IDChamado) AS NumberOfAnswers
                    FROM
                        '.TBL_CALLED.' c
                    LEFT JOIN
                        '.TBL_CALLED_HISTORY.' hc
                    ON
                        c.IDChamado = hc.IDChamado
                    WHERE
                        c.IDSetor = "' . $IDSetor . '" AND c.EnStatus = "FECHADO" AND
                        c.EnTipoChamado <> "TELEFONICO"  and hc.IDAcaoChamado = "' . AcaoChamado::RESPOSTA . '"
                         ' . TQuery::formatDateFilter('hc.DtResposta', $ArPeriod) . '
                    GROUP BY
                        hc.IDChamado
                ) SubQuery
            WHERE
                NumberOfAnswers = 1
        ';
    }

    public static function getSqlReportTotalCustomerBySector($IDSetor, $ArPeriod) {
        return'
            SELECT
                COUNT( c.IDCliente ) AS ReportCustomer
            FROM
                '.TBL_CUSTOMER.' c
            LEFT JOIN
                '.TBL_USER.' u ON u.IDUsuario = c.IDUsuario
            LEFT JOIN
                '.TBL_SECTOR_USER.' us ON us.IDUsuario = u.IDUsuario
            LEFT JOIN
                '.TBL_SECTOR.' s ON s.IDSetor = us.IDSetor
            WHERE
                s.IDSetor =  "'.$IDSetor.'"
        ' . TQuery::formatDateFilter('DtInscricao', $ArPeriod);
    }

    public static function getSqlFindAllEvaluationsBySector($IDSetor, $ArPeriod) {
        return '
            SELECT
                ac.*
            FROM
                '.TBL_CALLED.' c
            RIGHT JOIN
                '.TBL_CALLED_EVALUATION.' ac
            ON
                ac.IDChamado = c.IDChamado
            WHERE
                c.IDSetor = "' . $IDSetor . '"
        ' . TQuery::formatDateFilter('c.DtFechamento', $ArPeriod);
    }

    public static function getSqlTotalInternalCalledByDepartament($IDDepartamento, $ArPeriod) {
        return '
            SELECT
                    COUNT(hc.IDHistoricoChamado) AS NumberInternalCalled
            FROM
                    '.TBL_SECTOR.' s
            LEFT JOIN
                    '.TBL_CALLED.' c
            ON
                    c.IDSetor = s.IDSetor
            LEFT JOIN
                    '.TBL_CALLED_HISTORY.' hc
            ON
                    c.IDChamado=hc.IDchamado
            WHERE
                    s.IDDepartamento ="' . $IDDepartamento . '" AND  hc.IDAcaoChamado = "' . AcaoChamado::RESPOSTA . '"
                    AND c.EnTipoChamado = "INTERNO" AND hc.IDUsuario IS NOT NULL
        ' . TQuery::formatDateFilter('hc.DtResposta', $ArPeriod);
    }

    public static function getSqlTotalExternalCalledByDepartament($IDDepartamento, $ArPeriod) {
        return '
            SELECT
                    COUNT(hc.IDHistoricoChamado) AS NumberExternalCalled
            FROM
                    '.TBL_SECTOR.' s
            LEFT JOIN
                    '.TBL_CALLED.' c
            ON
                    s.IDSetor = c.IDSetor
            LEFT JOIN
                    '.TBL_CALLED_HISTORY.' hc
            ON
                    c.IDChamado=hc.IDchamado
            WHERE
                    s.IDDepartamento ="' . $IDDepartamento . '" AND  hc.IDAcaoChamado = "' . AcaoChamado::RESPOSTA . '"
                    AND c.EnTipoChamado = "EXTERNO" AND hc.IDUsuario IS NOT NULL
        ' . TQuery::formatDateFilter('hc.DtResposta', $ArPeriod);
    }

    public static function getSqlTotalPhoneCalledByDepartament($IDDepartamento, $ArPeriod) {
        return '
            SELECT
                COUNT(hc.IDHistoricoChamado) AS NumberPhoneCalled
          FROM
                '.TBL_SECTOR.' s
          LEFT JOIN
                '.TBL_CALLED.' c
          ON
                c.IDSetor = s.IDSetor
          LEFT JOIN
                '.TBL_CALLED_HISTORY.' hc
          ON
                c.IDChamado=hc.IDchamado
          WHERE
                s.IDDepartamento = "' . $IDDepartamento . '" AND  hc.IDAcaoChamado = "' . AcaoChamado::TELEFONICO . '"
                AND c.EnTipoChamado = "TELEFONICO" AND hc.IDUsuario IS NOT NULL
        ' . TQuery::formatDateFilter('hc.DtResposta', $ArPeriod);
    }

    public static function getSqlTotalNotClosedCalledByDepartament($IDDepartamento, $ArPeriod) {
        return '
            SELECT
                COUNT(c.IDChamado) AS NumberNotClosedCalled
            FROM
                '.TBL_SECTOR.' s
            LEFT JOIN
                '.TBL_CALLED.' c
            ON
                s.IDSetor = c.IDSetor
            LEFT JOIN
                '.TBL_CALLED_HISTORY.' hc
            ON
                hc.IDChamado = c.IDChamado and hc.IDCliente = c.IDCliente
            WHERE
                s.IDDepartamento = "'.$IDDepartamento.'" and c.EnTipoChamado = "EXTERNO" and c.EnStatus <> "FECHADO"
        ' . TQuery::formatDateFilter('hc.DtResposta', $ArPeriod);
    }

    public static function getSqlTotal1AnswerClosedCalledByDepartament($IDDepartamento, $ArPeriod) {
        return '
            SELECT
                COUNT(NumberOfAnswers) AS NumberOf1AnswerClosedCalled
            FROM
                (
                    SELECT
                        COUNT(hc.IDChamado) AS NumberOfAnswers
                    FROM
                        '.TBL_SECTOR.' s
                    LEFT JOIN
                        '.TBL_CALLED.' c
                    ON
                        s.IDSetor = c.IDSetor
                    LEFT JOIN
                        '.TBL_CALLED_HISTORY.' hc
                    ON
                        c.IDChamado = hc.IDChamado
                    WHERE
                        s.IDDepartamento = "' . $IDDepartamento . '" AND c.EnStatus = "FECHADO" AND
                        c.EnTipoChamado <> "TELEFONICO"  and hc.IDAcaoChamado = "' . AcaoChamado::RESPOSTA . '"
                         ' . TQuery::formatDateFilter('hc.DtResposta', $ArPeriod) . '
                    GROUP BY
                        hc.IDChamado
                ) SubQuery
            WHERE
                NumberOfAnswers = 1
        ';
    }

    public static function getSqlFindAllEvaluationsByDepartament($IDDepartamento, $ArPeriod) {
        return '
            SELECT
                ac.*
            FROM
                '.TBL_SECTOR.' s
            LEFT JOIN
                '.TBL_CALLED.' c
            ON
                c.IDSetor = s.IDSetor
            RIGHT JOIN
                '.TBL_CALLED_EVALUATION.' ac
            ON
                ac.IDChamado = c.IDChamado
            WHERE
                s.IDDepartamento = "' . $IDDepartamento . '"
        ' . TQuery::formatDateFilter('c.DtFechamento', $ArPeriod);
    }

    public static function getSqlReportTotalCustomerByDepartament($IDDepartamento, $ArPeriod) {
        return'
            SELECT
                COUNT( c.IDCliente ) AS ReportCustomer
            FROM
                '.TBL_CUSTOMER.' c
            LEFT JOIN
                '.TBL_USER.' u ON u.IDUsuario = c.IDUsuario
            LEFT JOIN
                '.TBL_SECTOR_USER.' us ON us.IDUsuario = u.IDUsuario
            LEFT JOIN
                '.TBL_SECTOR.' s ON s.IDSetor = us.IDSetor
            WHERE
                s.IDDepartamento =  "'.$IDDepartamento.'"
        ' . TQuery::formatDateFilter('DtInscricao', $ArPeriod);
    }

    public static function getSqlCancelCustomer($IDCliente) {
        return '
            UPDATE
                '.TBL_CUSTOMER.'
            SET
                EnStatus = "CANCELADO"
            WHERE
                IDCliente = "'.$IDCliente.'"
        ';
    }

    public static function getSqlUpdateCustomer($IDCliente, $ArDados) {
//TODO        StCep ="' .$ArDados['StCep'] .'",
        return '
            UPDATE
                '.TBL_CUSTOMER.'
            SET
                StEmail = "' .$ArDados['StEmail'] .'",  StEndereco = "' .$ArDados['StEndereco'] .'", StNumero = "' .$ArDados['StNumero'] .'", StComplemento = "' .$ArDados['StComplemento'] .'",
                StBairro ="' .$ArDados['StBairro'] . '", StCidade = "' .$ArDados['StCidade'] .'", StEstado="' .$ArDados['StEstado'] .'", StDddTelefone ="' .$ArDados['StDddTelefone'] .'", StTelefone = "' .$ArDados['StTelefone'] .'", StDddCelular = "' .$ArDados['StDddCelular'] .'", StCelular = "' .$ArDados['StCelular'] .'"
                    WHERE
            IDCliente ="' .$IDCliente . '"
            ';
    }

    public static function getSqlUpdateIndividual($IDCliente, $ArDados) {
        $StCargo = !empty($ArDados['Enterprise']['StCargo']) ? '"' . $ArDados['Enterprise']['StCargo'] . '"': '"NULL"' ;
        return '
            UPDATE
                '.TBL_INDIVIDUAL_CUSTOMER.'
            SET
                StNome = "' .$ArDados['StNome'] .'", StCpf = "' .$ArDados['StCpf'] .'", StRg = "' .$ArDados['StRg'] .'", DtNascimento = "' .$ArDados['DtNascimento'] .'", StCargo = ' .$StCargo .'
                    WHERE
            IDCliente ="' .$IDCliente . '"
            ';

    }

    public static function getSqlUpdateEnterprise ($IDCliente, $ArDados){
        return '
            UPDATE
                '.TBL_ENTERPRISE_CUSTOMER.'
            SET
                StRazaoSocial = "' .$ArDados['StRazaoSocial'] .'", StNomeFantasia = "' .$ArDados['StNomeFantasia'] .'", DtFundacao = "' .$ArDados['DtFundacao'] .'", StCnpj = "' .$ArDados['StCnpj'] .'"
                    WHERE
            IDCliente ="' .$IDCliente . '"
            ';

    }
    public static function getSqlFindProfileByPosition($IDFuncao) {
        return '
            SELECT
                *
            FROM
                '.TBL_PROFILE.'
            WHERE
                IDFuncao = "'.$IDFuncao.'"
        ';
    }

    public static function getSqlUpdateChangePositionUserById($IDNewPosition, $IDUser) {
        return '
            UPDATE
                '.TBL_USER.'
            SET
                IDFuncao = "'.$IDNewPosition.'"
            WHERE
                IDUsuario = "'.$IDUser.'"
        ';
    }

    public static function getSqlUpdateChangePositionProfileById($IDFuncao, $IDPerfil) {
        return '
            UPDATE
                '.TBL_PROFILE.'
            SET
                IDFuncao = "'.$IDFuncao.'"
            WHERE
                IDPerfil = "'.$IDPerfil.'"
        ';
    }

    public static function getSqlDeletePosition($IDFuncao) {
        return '
            DELETE FROM
                '.TBL_FUNCTION.'
            WHERE
                IDFuncao = "'.$IDFuncao.'"
        ';
    }

    public static function getSqlGenerateFinancialReport($IDFinanceiro) {
        return '
                SELECT
                    hs.IDHistoricoSituacao, NULL AS IDDebitos, NULL AS FoValor,hs.EnStatus, hs.DtData, NULL AS EnTipoPagamento
                FROM
                    '.TBL_FINANCIAL.' f
                LEFT JOIN
                    '.TBL_SITUATION_HISTORY.' hs
                ON
                    f.IDFinanceiro = hs.IDFinanceiro
                WHERE
                    hs.IDFinanceiro = "'.$IDFinanceiro.'"
            UNION
                SELECT
                    NULL AS IDHistoricoSituacao, l.IDDebitos, l.FoValor, l.EnStatus, l.DtData, l.EnTipoPagamento
                FROM
                    '.TBL_FINANCIAL.' f
                LEFT JOIN
                    '.TBL_RELEASES.' l
                ON
                    f.IDFinanceiro = l.IDFinanceiro
                WHERE
                    l.IDFinanceiro = "'.$IDFinanceiro.'"
             UNION
                SELECT
                    NULL AS IDHistoricoSituacao, c.IDDebito, c.FoValor, (SELECT "CONFIRMADO" FROM DUAL), c.DtData, c.EnTipoPagamento
                FROM
                    '.TBL_FINANCIAL.' f
                LEFT JOIN
                    '.TBL_RELEASES.' l
                ON
                    f.IDFinanceiro = l.IDFinanceiro
                RIGHT JOIN
                    '.TBL_CONFIRMATION.' c
                ON
                    l.IDDebitos = c.IDDebito
                WHERE
                    l.IDFinanceiro = "'.$IDFinanceiro.'"
            ORDER BY
                DtData DESC;
        ';
    }

    public static function getSqlFindReleasesByCustomer($IDCliente) {
        return '
            SELECT
                l.*
            FROM
                '.TBL_CUSTOMER.' c
            LEFT JOIN
                '.TBL_FINANCIAL.' f
            ON
                f.IDCliente = c.IDCliente
            RIGHT JOIN
                '.TBL_RELEASES.' l
            ON
                f.IDFinanceiro = l.IDFinanceiro
            WHERE
                c.IDCliente = "'.$IDCliente.'"
        ';
    }

    public static function getSqlFindCustomerByFinancial($IDFinanceiro) {
        return '
            SELECT
                pf.IDPessoaFisica, pf.StNome, pf.StCpf, pf.StRg, pf.DtNascimento, pf.StCargo,
                c.IDCliente, c.IDUsuario, c.StEmail, c.EnStatus, c.EnTipoCliente, c.DtInscricao, c.StEndereco, c.StNumero, c.StComplemento, c.StBairro,
                c.StCidade, c.StEstado, c.StCep, c.StDddTelefone, c.StTelefone, c.StDddCelular, c.StCelular,
                pj.IDPessoaJuridica, pj.IDRepresentante, pj.StRazaoSocial, pj.StNomeFantasia, pj.DtFundacao, pj.StCnpj,
                f.IDFinanceiro, f.EnTipoPagamento, f.EnPeriodicidade, f.ItDesconto
            FROM
                '.TBL_INDIVIDUAL_CUSTOMER.' pf
            LEFT JOIN
                '.TBL_CUSTOMER.' c
            ON
                c.IDCliente = pf.IDCliente
            LEFT JOIN
                '.TBL_ENTERPRISE_CUSTOMER.' pj
            ON
                pj.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_FINANCIAL.' f
            ON
                f.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_PLANS.' p
            ON
                f.IDPlano = p.IDPlano
            WHERE
                f.IDFinanceiro = "' . $IDFinanceiro . '"
        ';
    }

    public static function getSqlTotalMounthlyPaymentsByFinancial($IDFinanceiro) {
        return '
            SELECT
                COUNT(l.IDDebitos) AS NumberOfMounthlyPaymentsReleased
            FROM
                '.TBL_FINANCIAL.' f
            LEFT JOIN
                '.TBL_RELEASES.' l
            ON
                l.IDFinanceiro = f.IDFinanceiro
            WHERE
                f.IDFinanceiro = "'.$IDFinanceiro.'" AND l.EnStatus = "LANCADO"
        ';
    }

    public static function getSqlTotalReceivedValueFromFinancial($IDFinanceiro) {
        return '
            SELECT
                SUM(l.FoValor) AS ReceivedTotalValue
            FROM
                '.TBL_FINANCIAL.' f
            LEFT JOIN
                '.TBL_RELEASES.' l
            ON
                l.IDFinanceiro = f.IDFinanceiro
            WHERE
                f.IDFinanceiro = "'.$IDFinanceiro.'" AND l.EnStatus = "CONFIRMADO"
        ';
    }

    public static function getSqlTotalPaymentTypeByFinancial($IDFinanceiro, $EnTipoPagamento) {
        return '
            SELECT
                COUNT(f.IDFinanceiro) AS NumberOfPayments
            FROM
                '.TBL_FINANCIAL.' f
            LEFT JOIN
                '.TBL_RELEASES.' l
            ON
                l.IDFinanceiro = f.IDFinanceiro
            WHERE
                f.IDFinanceiro = "'.$IDFinanceiro.'" AND l.EnStatus = "CONFIRMADO" AND l.EnTipoPagamento = "'.$EnTipoPagamento.'"
        ';
    }

    public static function getSqlTotalSituationByFinancial($IDFinanceiro, $EnStatus) {
        return '
            SELECT
                COUNT(f.IDFinanceiro) AS NumberOfSituation
            FROM
                '.TBL_FINANCIAL.' f
            LEFT JOIN
                '.TBL_SITUATION_HISTORY.' hs
            ON
                hs.IDFinanceiro = f.IDFinanceiro
            WHERE
                f.IDFinanceiro = "'.$IDFinanceiro.'" AND hs.EnStatus = "'.$EnStatus.'"
        ';
    }

    public static function getSqlInsertReverse($IDFinanceiro, $FoValor, $DtData) {
        return '
            INSERT INTO
                '.TBL_RELEASES.'
                (IDFinanceiro, FoValor, EnStatus, DtData)
            VALUES
                ("'.$IDFinanceiro.'", "'.$FoValor.'", "ESTORNO", "'.$DtData.'")
        ';
    }

    public static function getSqlFindCustomerWithPendingMonthly() {
        return '
            SELECT
                pf.IDPessoaFisica, pf.StNome, pf.StCpf, pf.StRg, pf.DtNascimento, pf.StCargo,
                c.IDCliente, c.IDUsuario, c.StEmail, c.EnStatus, c.EnTipoCliente, c.DtInscricao, c.StEndereco, c.StNumero, c.StComplemento, c.StBairro,
                c.StCidade, c.StEstado, c.StCep, c.StDddTelefone, c.StTelefone, c.StDddCelular, c.StCelular,
                pj.IDPessoaJuridica, pj.IDRepresentante, pj.StRazaoSocial, pj.StNomeFantasia, pj.DtFundacao, pj.StCnpj,
                f.IDFinanceiro, f.EnTipoPagamento, f.EnPeriodicidade, f.ItDesconto
            FROM
                '.TBL_INDIVIDUAL_CUSTOMER.' pf
            LEFT JOIN
                '.TBL_CUSTOMER.' c
            ON
                c.IDCliente = pf.IDCliente
            LEFT JOIN
                '.TBL_ENTERPRISE_CUSTOMER.' pj
            ON
                pj.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_FINANCIAL.' f
            ON
                f.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_RELEASES.' l
            ON
                l.IDFinanceiro = f.IDFinanceiro
            LEFT JOIN
                '.TBL_CONFIRMATION.' co
            ON
                co.IDDebito = l.IDDebitos
            WHERE
                l.EnStatus = "LANCADO" AND co.IDConfirmacao IS NULL
            GROUP BY
                c.IDCliente
        ';
    }

    public static function getSqlFindPendingReleasesByFinancial($IDFinanceiro) {
        return '
            SELECT
                l.*
            FROM
                '.TBL_FINANCIAL.' f
            LEFT JOIN
                '.TBL_RELEASES.' l
            ON
                f.IDFinanceiro = l.IDFinanceiro
            LEFT JOIN
                '.TBL_CONFIRMATION.' c
            ON
                c.IDDebito = l.IDDebitos
            WHERE
                f.IDFinanceiro = "'.$IDFinanceiro.'" AND c.IDConfirmacao IS NULL AND EnStatus = "LANCADO"
        ';
    }

    public static function getSqlInsertPaymentConfirmation($IDPayment, $ArPaymentData) {
        return '
            INSERT INTO
                '.TBL_CONFIRMATION.'
                (IDDebito, FoValor, EnTipoPagamento, DtData)
            VALUES
                ("'.$IDPayment.'", "'.$ArPaymentData['FoValor'].'", "'.$ArPaymentData['EnTipoPagamento'].'", "'.$ArPaymentData['DtData'].'")
        ';
    }

    public static function getSqlInsertPlans($ArPlanoData){
        return'
            INSERT INTO
                 '.TBL_PLANS.'
                     (StNome, FoPreco, ItDesconto, TxDescricao, EnStatus)
                VALUES
                ("' .$ArPlanoData['StNome'] .'", "' .$ArPlanoData['FoPreco'] .'",
                 "' .$ArPlanoData['ItDesconto'] .'","' .$ArPlanoData['TxDescricao'] .'", "ATIVADO")
        ';
    }


    public static function getSqlUpdatePlans($ArDados){
        return
        'UPDATE
            '.TBL_PLANS.'
        SET
        StNome = "' . $ArDados['StNome'] . '" , ItDesconto ="'. $ArDados['ItDesconto'] .'", FoPreco="'. $ArDados['FoPreco'] .'",
        TxDescricao ="'. $ArDados['TxDescricao'] .'"
            WHERE
         IDPlano = "'. $ArDados['IDPlano'] .'"
                ';
    }

     public static function getSqlUpdateDepartament($IDDepartamento, $StNome) {
        return '
            UPDATE
                '.TBL_DEPARTAMENT.'
            SET
                StDepartamento = "' . $StNome . '"
            WHERE
                IDDepartamento = "' . $IDDepartamento . '"
        ';
     }

    public static function getSqlFindPlansById($IDPlano){
        return '
            SELECT
                *
            FROM
                '.TBL_PLANS.'
            WHERE
                IDPlano = "' . $IDPlano . '"
        ';
    }

    public static function getSqlFindReleasesById($IDPayment) {
        return '
            SELECT
                *
            FROM
                '.TBL_RELEASES.'
            WHERE
                IDDebitos = "'.$IDPayment.'"
        ';
    }

    public static function getSqlFindAllDelinquentsCustomers() {
        return '
            SELECT
                *
            FROM
                (
                    SELECT c.IDCliente, hs.EnStatus, hs.IDFinanceiro
                    FROM '.TBL_CUSTOMER.' c
                    LEFT JOIN '.TBL_FINANCIAL.' f
                    ON f.IDCliente = c.IDCliente
                    RIGHT JOIN '.TBL_SITUATION_HISTORY.' hs
                    ON hs.Idfinanceiro = f.Idfinanceiro
                    ORDER BY hs.DtData DESC
                ) SubQuery
            WHERE
                EnStatus <> "LIBERADO"
            GROUP BY
                IDFinanceiro
            HAVING
                EnStatus <> "CANCELADO"
        ';
    }

    public static function getSqlFindCustomersInIdList($ArCustomersId) {
        return '
            SELECT
                pf.IDPessoaFisica, pf.StNome, pf.StCpf, pf.StRg, pf.DtNascimento, pf.StCargo,
                c.IDCliente, c.IDUsuario, c.StEmail, c.EnStatus, c.EnTipoCliente, c.DtInscricao, c.StEndereco, c.StNumero, c.StComplemento, c.StBairro,
                c.StCidade, c.StEstado, c.StCep, c.StDddTelefone, c.StTelefone, c.StDddCelular, c.StCelular,
                pj.IDPessoaJuridica, pj.IDRepresentante, pj.StRazaoSocial, pj.StNomeFantasia, pj.DtFundacao, pj.StCnpj,
                f.IDFinanceiro, f.EnTipoPagamento, f.EnPeriodicidade, f.ItDesconto
            FROM
                '.TBL_INDIVIDUAL_CUSTOMER.' pf
            LEFT JOIN
                '.TBL_CUSTOMER.' c
            ON
                c.IDCliente = pf.IDCliente
            LEFT JOIN
                '.TBL_ENTERPRISE_CUSTOMER.' pj
            ON
                pj.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_FINANCIAL.' f
            ON
                f.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_PLANS.' p
            ON
                f.IDPlano = p.IDPlano
            WHERE
                c.IDCliente IN ('.  implode($ArCustomersId, ', ').')
        ';
    }

    public static function getSqlFindConfirmationByRelease($IDLancamento) {
        return '
            SELECT
                c.*
            FROM
                '.TBL_RELEASES.' l
            RIGHT JOIN
                '.TBL_CONFIRMATION.' c
            ON
                c.IDDebito = l.IDDebitos
            WHERE
                c.IDDebito = "'.$IDLancamento.'"
        ';
    }

    public static function getSqlInsertHistorySituation($IDFinanceiro, $EnStatus) {
        return '
            INSERT INTO
                '.TBL_SITUATION_HISTORY.'
                (IDFinanceiro, DtData, EnStatus)
            VALUES
                ("'.$IDFinanceiro.'", "'. today('BD') .'", "'.$EnStatus.'")
        ';
    }

    public static function getSqlUpdateStatusCustomer($IDFinanceiro, $EnStatus) {
        return '
            UPDATE
                '.TBL_CUSTOMER.' c
            LEFT JOIN
                '.TBL_FINANCIAL.' f
            ON
                f.IDCliente = c.IDCliente
            SET
                c.EnStatus = "'.$EnStatus.'"
            WHERE
                f.IDFinanceiro = "'.$IDFinanceiro.'"
        ';
    }

    public static function getSqlFindCancelledCalledByStatus($EnStatus) {
        return '
            SELECT
                IDChamado, IDSetor, IDResponsavel, IDCliente, StTitulo,
                DtCriacao, DtFechamento, EnTipoChamado, EnStatus, EnPrioridade
            FROM
                '.TBL_CALLED.'
            WHERE
                EnTipoChamado = "CANCELAMENTO" AND EnStatus = "'.$EnStatus.'"
            ORDER BY
                EnPrioridade ASC, DtCriacao ASC
        ';
    }

    public static function getSqlFindUserByProfile($IDPerfil) {
        return '
            SELECT
                IDUsuario
            FROM
                '.TBL_USER.'
            WHERE
                IDPerfil = "'.$IDPerfil.'"
        ';
    }

    public static function getSqlUpdateChangeUserProfile($IDUsuario, $IDPerfil) {
        return '
            UPDATE
                '.TBL_USER.'
            SET
                IDPerfil = "'.$IDPerfil.'"
            WHERE
                IDUsuario = "'.$IDUsuario.'"
        ';
    }

    public static function getSqlDeleteProfile($IDPerfil) {
        return '
            DELETE FROM
                '.TBL_PROFILE.'
            WHERE
                IDPerfil = "'.$IDPerfil.'"
        ';
    }

    public static function getSqlDeletePermissionsByProfile($IDPerfil) {
        return '
            DELETE FROM
                '.TBL_ACTION_PROFILE.'
            WHERE
                IDPerfil = "'.$IDPerfil.'"
        ';
    }


    public static function getSqlFindEvaluationByUser($IDUsuario) {
        return '
            SELECT
                ac.*
            FROM
                '.TBL_CALLED_EVALUATION.' ac
            LEFT JOIN
                '.TBL_CALLED.' c
            ON
                c.IDChamado = ac.IDChamado
            WHERE
                c.IDResponsavel = "'.$IDUsuario.'"
        ';
    }

    public static function getSqlFindEvaluationBySector($IDSetor) {
        return '
            SELECT
                ac.*
            FROM
                '.TBL_CALLED_EVALUATION.' ac
            LEFT JOIN
                '.TBL_CALLED.' c
            ON
                c.IDChamado = ac.IDChamado
            WHERE
                c.IDSetor = "'.$IDSetor.'"
        ';
    }

    public static function getSqlFindEvaluationByDepartament($IDDepartamento) {
        return '
            SELECT
                ac.*
            FROM
                '.TBL_CALLED_EVALUATION.' ac
            LEFT JOIN
                '.TBL_CALLED.' c
            ON
                c.IDChamado = ac.IDChamado
            LEFT JOIN
                '.TBL_SECTOR.' s
            ON
                s.IDSetor = c.IDSetor
            WHERE
                s.IDDepartamento = "'.$IDDepartamento.'"
        ';
    }

    public static function getSqlCountNumberOfSubscriptionsByMonth($ItMonthNumber) {
        return '
            SELECT
                Month, COUNT(Month) AS NumberOfSubscriptions
            FROM
                (
                    SELECT MONTH(DtInscricao) AS Month
                    FROM '.TBL_CUSTOMER.'
                ) SubQuery
            WHERE
                Month = "'.$ItMonthNumber.'"
            GROUP BY
                Month
        ';

    }

    public static function getSqlCountNumberOfSubscriptionsByYear() {
        return '
            SELECT
                Year, COUNT(Year) AS NumberOfSubscriptions
            FROM
                (
                    SELECT Year(DtInscricao) AS Year
                    FROM '.TBL_CUSTOMER.'
                ) SubQuery
            GROUP BY
                Year
        ';
    }

    public static function getSqlCountNumberOfSubscriptionsByPeriod($InitialPeriod, $FinalPeriod) {
        return '
            SELECT
                COUNT(Day) AS NumberOfSubscriptions
            FROM
                (
                    SELECT DAY(DtInscricao) AS Day
                    FROM '.TBL_CUSTOMER.'
                ) SubQuery
             WHERE
                Day >= "'.$InitialPeriod.'" AND Day <= "'.$FinalPeriod.'"
        ';
    }

    public static function getSqlFindSituationByStatusOrderDesc($EnStatus) {
        return '
            SELECT
                IDFinanceiro, COUNT(IDFINANCEIRO) AS SituationTimes
            FROM
                '.TBL_SITUATION_HISTORY.'
            WHERE
                EnStatus = "'.$EnStatus.'"
            group by
                IDFinanceiro
            ORDER BY
                SituationTimes DESC
        ';
    }

    public static function getSqlGetOldersActiveCustomerInSystem($ItLimit) {
        return '
            SELECT
                pf.IDPessoaFisica, pf.StNome, pf.StCpf, pf.StRg, pf.DtNascimento, pf.StCargo,
                c.IDCliente, c.IDUsuario, c.StEmail, c.EnStatus, c.EnTipoCliente, c.DtInscricao, c.StEndereco, c.StNumero, c.StComplemento, c.StBairro,
                c.StCidade, c.StEstado, c.StCep, c.StDddTelefone, c.StTelefone, c.StDddCelular, c.StCelular,
                pj.IDPessoaJuridica, pj.IDRepresentante, pj.StRazaoSocial, pj.StNomeFantasia, pj.DtFundacao, pj.StCnpj,
                f.IDFinanceiro, f.EnTipoPagamento, f.EnPeriodicidade, f.ItDesconto
            FROM
                '.TBL_INDIVIDUAL_CUSTOMER.' pf
            LEFT JOIN
                '.TBL_CUSTOMER.' c
            ON
                c.IDCliente = pf.IDCliente
            LEFT JOIN
                '.TBL_ENTERPRISE_CUSTOMER.' pj
            ON
                pj.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_FINANCIAL.' f
            ON
                f.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_PLANS.' p
            ON
                f.IDPlano = p.IDPlano
            WHERE
                c.EnStatus <> "CANCELADO"
            ORDER BY
                DtInscricao LIMIT ' . $ItLimit
        ;
    }

    public static function getSqlGetValueActiveCustomerInSystem($ItLimit) {
        return '
            SELECT
                pf.IDPessoaFisica, pf.StNome, pf.StCpf, pf.StRg, pf.DtNascimento, pf.StCargo,
                c.IDCliente, c.IDUsuario, c.StEmail, c.EnStatus, c.EnTipoCliente, c.DtInscricao, c.StEndereco, c.StNumero, c.StComplemento, c.StBairro,
                c.StCidade, c.StEstado, c.StCep, c.StDddTelefone, c.StTelefone, c.StDddCelular, c.StCelular,
                pj.IDPessoaJuridica, pj.IDRepresentante, pj.StRazaoSocial, pj.StNomeFantasia, pj.DtFundacao, pj.StCnpj,
                f.IDFinanceiro, f.EnTipoPagamento, f.EnPeriodicidade, f.ItDesconto, SUM(co.FoValor) AS Total
            FROM
                '.TBL_INDIVIDUAL_CUSTOMER.' pf
            LEFT JOIN
                '.TBL_CUSTOMER.' c
            ON
                c.IDCliente = pf.IDCliente
            LEFT JOIN
                '.TBL_ENTERPRISE_CUSTOMER.' pj
            ON
                pj.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_FINANCIAL.' f
            ON
                f.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_RELEASES.' l
            ON
                l.IDFinanceiro = f.IDFinanceiro
            LEFT JOIN
                '.TBL_CONFIRMATION.' co
            ON
                co.IDConfirmacao = l.IDFinanceiro
            WHERE
                c.EnStatus <> "CANCELADO"
            GROUP BY
                c.IDCliente
            ORDER BY
                Total DESC
            LIMIT ' .
                $ItLimit;
    }

    public static function getSqlFindPuctualCustomers() {
        return '
            SELECT
                pf.IDPessoaFisica, pf.StNome, pf.StCpf, pf.StRg, pf.DtNascimento, pf.StCargo,
                c.IDCliente, c.IDUsuario, c.StEmail, c.EnStatus, c.EnTipoCliente, c.DtInscricao, c.StEndereco, c.StNumero, c.StComplemento, c.StBairro,
                c.StCidade, c.StEstado, c.StCep, c.StDddTelefone, c.StTelefone, c.StDddCelular, c.StCelular,
                pj.IDPessoaJuridica, pj.IDRepresentante, pj.StRazaoSocial, pj.StNomeFantasia, pj.DtFundacao, pj.StCnpj,
                f.IDFinanceiro, f.EnTipoPagamento, f.EnPeriodicidade, f.ItDesconto
            FROM
                '.TBL_INDIVIDUAL_CUSTOMER.' pf
            LEFT JOIN
                '.TBL_CUSTOMER.' c
            ON
                c.IDCliente = pf.IDCliente
            LEFT JOIN
                '.TBL_ENTERPRISE_CUSTOMER.' pj
            ON
                pj.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_FINANCIAL.' f
            ON
                f.IDCliente = c.IDCliente
            WHERE
                NOT EXISTS (
                    SELECT NULL
                    FROM '.TBL_SITUATION_HISTORY.' hs
                    WHERE f.IDFinanceiro = hs.IDFinanceiro AND hs.EnStatus IN ("ATRASO")
                    GROUP BY hs.IDFinanceiro
                )
            ORDER BY
                c.DtInscricao ASC
        ';
    }

    public static function getSqlFindCustomersWithMoreCalledByCalledType($EnStatus, $ArStatus) {
        return '
            SELECT
                pf.IDPessoaFisica, pf.StNome, pf.StCpf, pf.StRg, pf.DtNascimento, pf.StCargo,
                c.IDCliente, c.IDUsuario, c.StEmail, c.EnStatus, c.EnTipoCliente, c.DtInscricao, c.StEndereco, c.StNumero, c.StComplemento, c.StBairro,
                c.StCidade, c.StEstado, c.StCep, c.StDddTelefone, c.StTelefone, c.StDddCelular, c.StCelular,
                pj.IDPessoaJuridica, pj.IDRepresentante, pj.StRazaoSocial, pj.StNomeFantasia, pj.DtFundacao, pj.StCnpj,
                f.IDFinanceiro, f.EnTipoPagamento, f.EnPeriodicidade, f.ItDesconto, COUNT(ch.IDCliente) AS NumberOfCalled
            FROM
                '.TBL_CALLED.' ch
            LEFT JOIN
                '.TBL_INDIVIDUAL_CUSTOMER.' pf
            ON
                pf.IDCliente = ch.IDCliente
            LEFT JOIN
                '.TBL_CUSTOMER.' c
            ON
                c.IDCliente = pf.IDCliente
            LEFT JOIN
                '.TBL_ENTERPRISE_CUSTOMER.' pj
            ON
                pj.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_FINANCIAL.' f
            ON
                f.IDCliente = c.IDCliente
            WHERE
                ch.EnTipoChamado = "'.$EnStatus.'" AND ch.EnStatus IN ("'. implode('", "', $ArStatus) .'")
            GROUP BY
                c.IDCliente
            ORDER BY
                NumberOfCalled DESC
        ';
    }

    public static function getSqlFindCustomersWithMorePerfectCalled() {
        return '
            SELECT
                *, SUM(NumberOfAnswers) AS NumberOfPerfectCalled
            FROM
                (
                    SELECT
                        pf.IDPessoaFisica, pf.StNome, pf.StCpf, pf.StRg, pf.DtNascimento, pf.StCargo,
                        c.IDCliente, c.IDUsuario, c.StEmail, c.EnStatus, c.EnTipoCliente, c.DtInscricao, c.StEndereco, c.StNumero, c.StComplemento, c.StBairro,
                        c.StCidade, c.StEstado, c.StCep, c.StDddTelefone, c.StTelefone, c.StDddCelular, c.StCelular,
                        pj.IDPessoaJuridica, pj.IDRepresentante, pj.StRazaoSocial, pj.StNomeFantasia, pj.DtFundacao, pj.StCnpj,
                        f.IDFinanceiro, f.EnTipoPagamento, f.EnPeriodicidade, f.ItDesconto, COUNT(co.IDChamado) AS NumberOfAnswers
                    FROM
                        '.TBL_CALLED_HISTORY.' hc
                    LEFT JOIN
                        '.TBL_CALLED.' co
                    ON
                        co.IDChamado = hc.IDChamado
                    LEFT JOIN
                        '.TBL_INDIVIDUAL_CUSTOMER.' pf
                    ON
                        pf.IDCliente = co.IDCliente
                    LEFT JOIN
                        '.TBL_CUSTOMER.' c
                    ON
                        c.IDCliente = pf.IDCliente
                    LEFT JOIN
                        '.TBL_ENTERPRISE_CUSTOMER.' pj
                    ON
                        pj.IDCliente = c.IDCliente
                    LEFT JOIN
                        '.TBL_FINANCIAL.' f
                    ON
                        f.IDCliente = c.IDCliente
                    WHERE
                        co.EnStatus = "FECHADO" AND co.EnTipoChamado = "EXTERNO"
                        AND hc.IDAcaoChamado = "1" AND hc.IDUsuario IS NULL
                    GROUP BY
                        hc.IDChamado, c.IDCliente
                    HAVING
                        NumberOfAnswers = 1
                ) SubQuery
            GROUP BY
                IDCliente
        ';
    }

    public static function getSqlCountNumberOfCancelledCustomersByPeriod($InitialPeriod, $FinalPeriod) {
        return '
            SELECT
                COUNT(Day) AS NumberOfCancelledCustomers
            FROM
                (
                    SELECT DAY(hs.DtData) AS Day
                    FROM '.TBL_CUSTOMER.' c
                    LEFT JOIN '.TBL_FINANCIAL.' f
                    ON f.IDCliente = c.IDCliente
                    RIGHT JOIN '.TBL_SITUATION_HISTORY.' hs
                    ON hs.IDFinanceiro = f.IDFinanceiro
                    WHERE hs.EnStatus = "CANCELADO"
                ) SubQuery
             WHERE
                Day >= "'.$InitialPeriod.'" AND Day <= "'.$FinalPeriod.'"';
    }

    public static function getSqlCountNumberOfCancelledCustomersByYear() {
        return '
            SELECT
                Year, COUNT(Year) AS NumberOfCancelledCustomers
            FROM
                (
                    SELECT Year(hs.DtData) AS Year
                    FROM '.TBL_CUSTOMER.' c
                    LEFT JOIN '.TBL_FINANCIAL.' f
                    ON f.IDCliente = c.IDCliente
                    RIGHT JOIN '.TBL_SITUATION_HISTORY.' hs
                    ON hs.IDFinanceiro = f.IDFinanceiro
                    WHERE hs.EnStatus = "CANCELADO"
                ) SubQuery
            GROUP BY
                Year';
    }

    public static function getSqlCountNumberOfCancelledCustomersByMonth($ItMonthNumber) {
        return '
            SELECT
                Month, COUNT(Month) AS NumberOfCancelledCustomers
            FROM
                (
                    SELECT Month(hs.DtData) AS Month
                    FROM '.TBL_CUSTOMER.' c
                    LEFT JOIN '.TBL_FINANCIAL.' f
                    ON f.IDCliente = c.IDCliente
                    RIGHT JOIN '.TBL_SITUATION_HISTORY.' hs
                    ON hs.IDFinanceiro = f.IDFinanceiro
                    WHERE hs.EnStatus = "CANCELADO"
                ) SubQuery
            WHERE
                Month = "'.$ItMonthNumber.'"
            GROUP BY
                Month';
    }

    public static function getSqlInsertAnnouncement($ArDados) {
        return '
            INSERT INTO
                '.TBL_ANNONCEMENT.'
                (IDCliente, StTitulo, StComunicado, EnTipoComunicado, DtCricacao)
            VALUES
                ("' . $ArDados['IDCliente'] . '", "' . $ArDados['StTitulo'] . '", "' . $ArDados['StComunicado'] . '", "' . $ArDados['EnTipoComunicado'] . '", now())
            ';
    }

    public static function getSqlFindAllAnnouncements() {
        return '
            SELECT
                IDComunicacao, IDCliente, StTitulo, StComunicado, EnTipoComunicado, DtCriacao
            FROM
                '.TBL_ANNONCEMENT.'
            ';
    }



    public static function getSqlFindAllEvaluationsByCustomer($IDCliente) {
        return '
            SELECT
                ac.*
            FROM
                '.TBL_CALLED.' c
            RIGHT JOIN
                '.TBL_CALLED_EVALUATION.' ac
            ON
                ac.IDChamado = c.IDChamado
            WHERE
                c.IDCliente = "' . $IDCliente . '"';
    }

    public static function getSqlFindCustomerWithMoreEvaluationType($EnTipoAvaliacao) {
        return '
            SELECT
                cl.IDCliente, COUNT(IDAvaliacaoChamado) AS NumberOfEvaluations
            FROM
                '.TBL_CALLED_EVALUATION.' ac
            LEFT JOIN
                '.TBL_CALLED.' ch
            ON
                ch.IDChamado = ac.IDChamado
            LEFT JOIN
                '.TBL_CUSTOMER.' cl
            ON
                cl.IDCliente = ch.IDCliente
            WHERE
                EnAvaliacao = "'.$EnTipoAvaliacao.'"
            GROUP BY
                cl.IDCliente
            ORDER BY
                NumberOfEvaluations
            LIMIT
                10
        ';
    }

    public static function getSqlCountNumberOfEvaluationsByCustomer() {
        return '
            SELECT
                c.IDCliente, COUNT(ac.IDAvaliacaoChamado) AS NumberOfEvaluations
            FROM
                '.TBL_CALLED_EVALUATION.' ac
            LEFT JOIN
                '.TBL_CALLED.' c
            ON
                c.IDChamado = ac.IDChamado
            GROUP BY
                c.IDCliente
        ';
    }

    public static function getSqlFindAllAnnouncementByCustomer($IDCliente) {
        return '
            SELECT
                *
            FROM
                '.TBL_ANNONCEMENT.'
            WHERE
                IDCliente = "'.$IDCliente.'"
        ';
    }

    public static function getSqlFindCustomerWithMoreAnnouncementType($EnTipoComunicado) {
        return '
            SELECT
                IDCliente, COUNT(IDComunicacao) AS NumberOfAnnouncement
            FROM
                '.TBL_ANNONCEMENT.'
            WHERE
                EnTipoComunicado = "'.$EnTipoComunicado.'"
            GROUP BY
                IDCliente
            ORDER BY
                NumberOfAnnouncement
            LIMIT
                10
        ';
    }

    public static function getSqlFindAnnouncementById($IDComunicacao) {
        return '
            SELECT
                *
            FROM
                '.TBL_ANNONCEMENT.'
            WHERE
                IDComunicacao = "' . $IDComunicacao . '"
        ';
    }

    public static function getSqlFindUsersWithPendingPayment() {
        return '
            SELECT
                u.IDUsuario, NumberOfPayments, AdmissionMonth, AdmissionYear, (YEAR(now())-AdmissionYear)*12+(12-AdmissionMonth) AS NumberOfPaymentsToDo
            FROM
                '.TBL_USER.' u
            LEFT JOIN
                (
                    SELECT
                        u.IDUsuario, COUNT(p.IDAdministracao) AS NumberOfPayments, MONTH(a.DtAdmissao) AS AdmissionMonth, YEAR(a.DtAdmissao) AS AdmissionYear
                    FROM
                        '.TBL_USER.' u
                    LEFT JOIN
                        '.TBL_ADMINISTRATION.' a
                    ON
                        a.IDUsuario = u.IDUsuario
                    LEFT JOIN
                        '.TBL_PAYMENT.' p
                    ON
                        p.IDAdministracao = a.IDAdministracao
                    GROUP BY
                        u.IDUsuario
                    ORDER BY
                        u.StNome ASC
                ) SubQuery
            ON
                SubQuery.IDUsuario = u.IDUsuario
            WHERE
                (YEAR(now())-AdmissionYear)*12+(12-AdmissionMonth)-1 > NumberOfPayments
        ';
    }

    public static function getSqlFindAllPaymentsByUser($IDUsuario) {
        return '
            SELECT
                p.*
            FROM
                '.TBL_USER.' u
            LEFT JOIN
                '.TBL_ADMINISTRATION.' a
            ON
                u.IDUsuario = a.IDUsuario
            RIGHT JOIN
                '.TBL_PAYMENT.' p
            ON
                p.IDAdministracao = a.IDAdministracao
            WHERE
                u.IDUsuario = "'.$IDUsuario.'"
        ';
    }

    public static function getSqlInsertUserPayment($IDAdministracao, $StPeriod, $ArPaymentData) {
        return '
            INSERT INTO
                '.TBL_PAYMENT.'
                (IDAdministracao, FoSalarioLiquido, FoDescontos, FoBonificacoes, DtDiaPagamento, StPeriodo)
            VALUES
                ("'.$IDAdministracao.'", "'.$ArPaymentData['FoSalarioLiquido'].'", "'.$ArPaymentData['FoDescontos'].'", "'.$ArPaymentData['FoBonificacoes'].'", "'.$ArPaymentData['DtDiaPagamento'].'", "'.$StPeriod.'")
        ';
    }

    public static function getSqlFindUsersInTimelyPayment() {
        return '
            SELECT
                u.IDUsuario, NumberOfPayments, AdmissionMonth, AdmissionYear, (YEAR(now())-AdmissionYear)*12+(12-AdmissionMonth) AS NumberOfPaymentsToDo
            FROM
                '.TBL_USER.' u
            LEFT JOIN
                (
                    SELECT
                        u.IDUsuario, COUNT(p.IDAdministracao) AS NumberOfPayments, MONTH(a.DtAdmissao) AS AdmissionMonth, YEAR(a.DtAdmissao) AS AdmissionYear
                    FROM
                        '.TBL_USER.' u
                    LEFT JOIN
                        '.TBL_ADMINISTRATION.' a
                    ON
                        a.IDUsuario = u.IDUsuario
                    LEFT JOIN
                        '.TBL_PAYMENT.' p
                    ON
                        p.IDAdministracao = a.IDAdministracao
                    GROUP BY
                        u.IDUsuario
                    ORDER BY
                        u.StNome ASC
                ) SubQuery
            ON
                SubQuery.IDUsuario = u.IDUsuario
            WHERE
                (YEAR(now())-AdmissionYear)*12+(12-AdmissionMonth)-1 <= NumberOfPayments
    ';
    }

    public static function getSqlFindEvaluationByCalled($IDChamado) {
        return '
            SELECT
                *
            FROM
                '.TBL_CALLED_EVALUATION.'
            WHERE
                IDChamado = "'.$IDChamado.'"
        ';
    }

    public static function getSqlDisablePlan($IDPlano) {
        return '
            UPDATE
                '.TBL_PLANS.'
            SET
                EnStatus = "DESATIVADO"
            WHERE
                IDPlano = "'.$IDPlano.'"
        ';
    }

    public static function getSqlEnablePlan($IDPlano) {
        return '
            UPDATE
                '.TBL_PLANS.'
            SET
                EnStatus = "ATIVADO"
            WHERE
                IDPlano = "'.$IDPlano.'"
        ';
    }

    public static function getSqlFindCustomerBySituation($EnSituacao) {
        return '
            SELECT
                IDCliente, EnStatus
            FROM
                '. TBL_CUSTOMER .'
            WHERE
                EnStatus = "'.$EnSituacao.'"
        ';
    }

    public static function getSqlPermissionControlLoad($IDAcao, $IDPerfil) {
        return '
            SELECT
                ca.*
            FROM
                '.TBL_PROFILE.' p
            RIGHT JOIN
                '.TBL_ACTION_PROFILE.' pa
            ON
                pa.IDPerfil = p.IDPerfil
            LEFT JOIN
                '.TBL_ACTION.' a
            ON
                a.IDAcao = pa.IDAcao
            RIGHT JOIN
                '.TBL_ACTION_CONTROLLER.' ca
            ON
                ca.IDAcao = a.IDAcao
            WHERE
                a.IDAcao = "'.$IDAcao.'" AND p.IDPerfil = "'.$IDPerfil.'"
        ';
    }

    public static function getSqlFindAllFreeActions() {
        return '
            SELECT
                a.*, p.IDPerfil
            FROM
                '.TBL_ACTION.' a
            LEFT JOIN
                '.TBL_ACTION_PROFILE.' pa
            ON
                pa.IDAcao = a.IDAcao
            LEFT JOIN
                '.TBL_PROFILE.' p
            ON
                p.IDPerfil = pa.IDPerfil
            WHERE
                EnRestrito = "FALSE"
        ';
    }

    public static function getSqlFindControlAccessByAction($IDAcao) {
        return '
            SELECT
                *
            FROM
                '.TBL_ACTION_CONTROLLER.'
            WHERE
                IDAcao = "'.$IDAcao.'"
        ';
    }

    public static function getSqlFindAllRestrictActions() {
        return '
            SELECT
                *
            FROM
                '.TBL_ACTION.'
            WHERE
                EnRestrito = "TRUE"
            ORDER BY
                StAcao
        ';
    }

    public static function getSqlFindAllAllowedSubmenuByProfile($IDPerfil) {
        return '
            SELECT
                *
            FROM
                '.TBL_MENU.' m
            RIGHT JOIN
                '.TBL_SUBMENU.' s
            ON
                m.IDMenu = s.IDMenu
            LEFT JOIN
                '.TBL_ACTION_CONTROLLER.' ca
            ON
                ca.IDControleAcao = s.IDControleAcao
            LEFT JOIN
                '.TBL_ACTION.' a
            ON
                a.IDAcao = ca.IDAcao
            LEFT JOIN
                '.TBL_ACTION_PROFILE.' pa
            ON
                pa.IDAcao = a.IDAcao
            LEFT JOIN
                '.TBL_PROFILE.' p
            ON
                p.IDPerfil = pa.IDPerfil
            WHERE
                s.IDControleAcao IS NULL OR p.IDPerfil = "'.$IDPerfil.'" OR a.EnRestrito = "FALSE"
            ORDER BY
                 m.ItOrdem, s.ItOrdem
        ';
    }

    public static function getSqlFindAllAllowedMenu($IDPerfil) {
        return '
            SELECT
                m.*
            FROM
                '.TBL_MENU.' m
            LEFT JOIN
                '.TBL_SUBMENU.' s
            ON
                m.IDMenu = s.IDMenu
            LEFT JOIN
                '.TBL_ACTION_CONTROLLER.' ca
            ON
                ca.IDControleAcao = s.IDControleAcao
            LEFT JOIN
                '.TBL_ACTION.' a
            ON
                a.IDAcao = ca.IDAcao
            LEFT JOIN
                '.TBL_ACTION_PROFILE.' pa
            ON
                pa.IDAcao = a.IDAcao
            LEFT JOIN
                '.TBL_PROFILE.' p
            ON
                p.IDPerfil = pa.IDPerfil
            WHERE
                p.IDPerfil = "'.$IDPerfil.'" or a.EnRestrito = "FALSE"
            GROUP BY
                IDMenu
            ORDER BY
                m.ItOrdem, s.IDSubmenu;
        ';
    }

    public static function getSqlFindAllActiveCustomerBoleto() {
        return '
        SELECT
                pf.IDPessoaFisica, pf.StNome, pf.StCpf, pf.StRg, pf.DtNascimento, pf.StCargo,
                c.IDCliente, c.IDUsuario, c.StEmail, c.EnStatus, c.EnTipoCliente, c.DtInscricao, c.StEndereco, c.StNumero, c.StComplemento, c.StBairro,
                c.StCidade, c.StEstado, c.StCep, c.StDddTelefone, c.StTelefone, c.StDddCelular, c.StCelular,
                pj.IDPessoaJuridica, pj.IDRepresentante, pj.StRazaoSocial, pj.StNomeFantasia, pj.DtFundacao, pj.StCnpj,
                f.IDFinanceiro, f.EnTipoPagamento, f.EnPeriodicidade, f.ItDesconto
            FROM
                '.TBL_INDIVIDUAL_CUSTOMER.' pf
            LEFT JOIN
                '.TBL_CUSTOMER.' c
            ON
                c.IDCliente = pf.IDCliente
            LEFT JOIN
                '.TBL_ENTERPRISE_CUSTOMER.' pj
            ON
                pj.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_FINANCIAL.' f
            ON
                f.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_PLANS.' p
            ON
                f.IDPlano = p.IDPlano
            WHERE
                f.EnTipoPagamento = "BOLETO"
                AND
                c.EnStatus <> "CANCELADO"
        ';
    }

    public static function getSqlFindAllBlockedMessagesBySector($IDSetor) {
        return '
            SELECT
                hc.*, ac.StAcao
            FROM
                '.TBL_CALLED.' c
            LEFT JOIN
                '.TBL_CALLED_HISTORY.' hc
            ON
                c.IDChamado = hc.IDChamado
            LEFT JOIN
                '.TBL_CALLED_ACTIONS.' ac
            ON
                ac.IDAcaoChamado = hc.IDAcaoChamado
            WHERE
                hc.EnStatus = "AGUARDANDO LIBERACAO" and c.IDSetor = "'.$IDSetor.'"
        ';
    }

    public static function getSqlFindMessageById($IDMensagem) {
        return '
            SELECT
                hc.*, ac.StAcao
            FROM
                '.TBL_CALLED_HISTORY.' hc
            LEFT JOIN
                '.TBL_CALLED_ACTIONS.' ac
            ON
                ac.IDAcaoChamado = hc.IDAcaoChamado
            WHERE
                hc.IDHistoricoChamado = "'.$IDMensagem.'"
        ';
    }

    public static function getSqlBlockMessage($IDMensagem) {
        return '
            UPDATE
                '.TBL_CALLED_HISTORY.'
            SET
                EnStatus = "BLOQUEADO"
            WHERE
                IDHistoricoChamado = "'.$IDMensagem.'"
        ';
    }

    public static function getSqlUnblockMessage($IDMensagem) {
        return '
            UPDATE
                '.TBL_CALLED_HISTORY.'
            SET
                EnStatus = "LIBERADO"
            WHERE
                IDHistoricoChamado = "'.$IDMensagem.'"
        ';
    }

    public static function getSqlUpdateFinancialById($ArDados) {
        return '
            UPDATE
                '.TBL_FINANCIAL.'
            SET
                IDPlano = "'.$ArDados['IDPlano'].'", EnPeriodicidade = "'.$ArDados['EnPeriodicidade'].'", EnTipoPagamento = "'.$ArDados['EnTipoPagamento'].'", ItDesconto = "'.$ArDados['ItDesconto'].'"
            WHERE
                IDFinanceiro = "'.$ArDados['IDFinanceiro'].'"
        ';
    }

    public static function getSqlFindBirthdaysIn($ItDays, $ItLimit = false) {
        $ItLimit = $ItLimit ? 'LIMIT ' . $ItLimit : '';
        return '
            SELECT
                IDUsuario
            FROM
                '.TBL_USER.'
            WHERE
                if(
                    DATEDIFF(
                        CONCAT(YEAR(now()), "-", MONTH(DtNascimento), "-", DAY(DtNascimento)),
                        now()
                    ) > 0,
                    DATEDIFF(
                        CONCAT(YEAR(now()), "-", MONTH(DtNascimento), "-", DAY(DtNascimento)),
                        now()
                    ) < '.$ItDays.',
                    DATEDIFF(
                        CONCAT(YEAR(now())+1, "-", MONTH(DtNascimento), "-", DAY(DtNascimento)),
                        now()
                    ) < '.$ItDays.'
                )
            ORDER BY
                if(
                    DATEDIFF(
                        CONCAT(YEAR(now()), "-", MONTH(DtNascimento), "-", DAY(DtNascimento)),
                        now()
                    ) > 0,
                        CONCAT(YEAR(now()), "-", MONTH(DtNascimento), "-", DAY(DtNascimento))
                    ,
                        CONCAT(YEAR(now())+1, "-", MONTH(DtNascimento), "-", DAY(DtNascimento))
                ) ASC
        ' . $ItLimit;
    }

    public static function getSqlFindCustomerByPayment($IDCobranca) {
        return '
            SELECT
                pf.IDPessoaFisica, pf.StNome, pf.StCpf, pf.StRg, pf.DtNascimento, pf.StCargo,
                c.IDCliente, c.IDUsuario, c.StEmail, c.EnStatus, c.EnTipoCliente, c.DtInscricao, c.StEndereco, c.StNumero, c.StComplemento, c.StBairro,
                c.StCidade, c.StEstado, c.StCep, c.StDddTelefone, c.StTelefone, c.StDddCelular, c.StCelular,
                pj.IDPessoaJuridica, pj.IDRepresentante, pj.StRazaoSocial, pj.StNomeFantasia, pj.DtFundacao, pj.StCnpj,
                f.IDFinanceiro, f.EnTipoPagamento, f.EnPeriodicidade, f.ItDesconto
            FROM
                '.TBL_INDIVIDUAL_CUSTOMER.' pf
            LEFT JOIN
                '.TBL_CUSTOMER.' c
            ON
                c.IDCliente = pf.IDCliente
            LEFT JOIN
                '.TBL_ENTERPRISE_CUSTOMER.' pj
            ON
                pj.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_FINANCIAL.' f
            ON
                f.IDCliente = c.IDCliente
            LEFT JOIN
                '.TBL_RELEASES.' l
            ON
                l.IDFinanceiro = f.IDFinanceiro
            LEFT JOIN
                '.TBL_PLANS.' p
            ON
                f.IDPlano = p.IDPlano
            WHERE
                l.IDDebitos = '.$IDCobranca.'
            ';
    }

    public static function getSqlFindNoticesByUser($Usuario, $ItLimit = false) {
        $ItLimit = $ItLimit ? 'LIMIT ' . $ItLimit : '';
        return '
            SELECT
                *
            FROM
                '.TBL_NOTICES.' a
            LEFT JOIN
                '.TBL_NOTICE_CATEGORY.' ca
            ON
                ca.IDCategoria = a.IDCategoria
            WHERE
                (a.IDSetor = "'.$Usuario->getTDepartamento()->getSetores(true)->getIDSetor().'" OR a.IDSetor IS NULL) AND (a.DtInicio < now() AND a.DtDesativar > now())
        ' . $ItLimit;
    }

    public static function getSqlFindNoticesById($IDAviso) {
        return '
            SELECT
                *
            FROM
                '.TBL_NOTICES.' a
            LEFT JOIN
                '.TBL_NOTICE_CATEGORY.' ca
            ON
                ca.IDCategoria = a.IDCategoria
            WHERE
                a.IDAviso = "'.$IDAviso.'"
        ';
    }

    public static function getSqlFindAllNotices() {
        return '
            SELECT
                *
            FROM
                '.TBL_NOTICES.' a
            LEFT JOIN
                '.TBL_NOTICE_CATEGORY.' ca
            ON
                ca.IDCategoria = a.IDCategoria
            WHERE
                DtInicio < now() AND DtDesativar > now()
        ';
    }

    public static function getSqlFindAllNoticesCategories() {
        return '
            SELECT
                *
            FROM
                '.TBL_NOTICE_CATEGORY.'
        ';
    }

    public static function getSqlInsertNotice($ArDados, $IDUsuario) {
        $IDSetor = empty($ArDados['IDSetor']) ? 'NULL' : '"'.$ArDados['IDSetor'].'"';
        return '
            INSERT INTO
                '.TBL_NOTICES.'
                (IDCategoria, IDUsuario, IDSetor, StTitulo,
                TxAviso, DtCriacao, DtInicio, DtDesativar)
            VALUES
                ("'.$ArDados['IDCategoria'].'", "'.$IDUsuario.'", '.$IDSetor.', "'.$ArDados['StTitulo'].'",
                 "'.$ArDados['TxAviso'].'", "'.now().'", "'.$ArDados['DtInicio'].'", "'.$ArDados['DtDesativar'].'")
        ';
    }

    public static function getSqlUpdateNotice($ArDados) {
        $IDSetor = empty($ArDados['IDSetor']) ? 'NULL' : '"'.$ArDados['IDSetor'].'"';
        return '
            UPDATE
                '.TBL_NOTICES.'
            SET
                IDCategoria = "'.$ArDados['IDCategoria'].'", IDSetor = '.$IDSetor.',
                StTitulo = "'.$ArDados['StTitulo'].'", TxAviso = "'.$ArDados['TxAviso'].'",
                DtInicio = "'.$ArDados['DtInicio'].'", DtDesativar = "'.$ArDados['DtDesativar'].'"
            WHERE
                IDAviso = "'.$ArDados['IDAviso'].'"
        ';
    }

    public static function getSqlFindEnabledPlans() {
        return '
            SELECT
                *
            FROM
                '.TBL_PLANS.'
            WHERE
                EnStatus = "ATIVADO"
        ';
    }

    public static function getSqlFindNotesByUser($IDUsuario, $ItLimit = false) {
        $ItLimit = $ItLimit ? 'LIMIT ' . $ItLimit : '';
        return '
            SELECT
                *
            FROM
                '.TBL_ANNOTATIONS.'
            WHERE
                IDUsuario = "'.$IDUsuario.'"
        ' . $ItLimit;
    }

    public static function getSqlFindNotesById($IDAnotacao) {
        return '
            SELECT
                *
            FROM
                '.TBL_ANNOTATIONS.'
            WHERE
                IDAnotacao = "'.$IDAnotacao.'"
        ';
    }

    public static function getSqlInsertNote($IDUsuario, $ArAnotacaoDados) {
        return '
            INSERT INTO
                '.TBL_ANNOTATIONS.'
                (IDUsuario, StTitulo, TxAnotacao)
            VALUES
                ("'.$IDUsuario.'", "'.$ArAnotacaoDados['StTitulo'].'", "'.$ArAnotacaoDados['TxAnotacao'].'")
        ';
    }

    public static function getSqlUpdateNote($IDUsuario, $ArDados) {
        return '
            UPDATE
                '.TBL_ANNOTATIONS.'
            SET
                IDUsuario = "'.$IDUsuario.'", StTitulo = "'.$ArDados['StTitulo'].'", TxAnotacao = "'.$ArDados['TxAnotacao'].'"
            WHERE
                IDAnotacao = "'.$ArDados['IDAnotacao'].'"
        ';
    }

    public static function getSqlDeleteNote($IDAnotacao) {
        return '
            DELETE FROM
                '.TBL_ANNOTATIONS.'
            WHERE
                IDAnotacao = "'.$IDAnotacao.'"
        ';
    }

    public static function getSqlFindFavedCalledByUser($IDUsuario) {
        return '
            SELECT
                c.*
            FROM
                '.TBL_USER.' u
            RIGHT JOIN
                '.TBL_PREFERRED_CALLED.' cp
            ON
                cp.IDUsuario = u.IDUsuario
            LEFT JOIN
                '.TBL_CALLED.' c
            ON
                cp.IDChamado = c.IDChamado
            WHERE
                u.IDUsuario = "'.$IDUsuario.'";
        ';
    }

    public static function getSqlAddToFavourite($IDChamado, $IDUsuario) {
        return '
            INSERT INTO
                '.TBL_PREFERRED_CALLED.'
            VALUES
                ("'.$IDChamado.'", "'.$IDUsuario.'")
        ';
    }

    public static function getSqlRemoveFromFavourite($IDChamado, $IDUsuario) {
        return '
            DELETE FROM
                '.TBL_PREFERRED_CALLED.'
            WHERE
                IDChamado = "'.$IDChamado.'" AND IDUsuario = "'.$IDUsuario.'"

        ';
    }

    public static function getSqlFindActiveNotices() {
        return '
            SELECT
                *
            FROM
                '.TBL_NOTICES.' a
            LEFT JOIN
                '.TBL_NOTICE_CATEGORY.' ca
            ON
                ca.IDCategoria = a.IDCategoria
            WHERE
                a.DtInicio < now() AND a.DtDesativar > now()
        ';
    }

    public static function getSqlFindExperedNotices() {
        return '
            SELECT
                *
            FROM
                '.TBL_NOTICES.' a
            LEFT JOIN
                '.TBL_NOTICE_CATEGORY.' ca
            ON
                ca.IDCategoria = a.IDCategoria
            WHERE
                a.DtDesativar < now()
        ';
    }

    public static function getSqlFindFutureNotices() {
        return '
            SELECT
                *
            FROM
                '.TBL_NOTICES.' a
            LEFT JOIN
                '.TBL_NOTICE_CATEGORY.' ca
            ON
                ca.IDCategoria = a.IDCategoria
            WHERE
                a.DtInicio > now()
        ';
    }

    public static function getSqlFindAllQuestions($ItLimit = false) {
        $ItLimit = $ItLimit ? 'LIMIT ' . $ItLimit : '';
        return '
            SELECT
                *
            FROM
                '.TBL_FAQ.'
        ' . $ItLimit;
    }

    public static function getSqlFindQuestionById($IDFaq) {
        return '
            SELECT
                *
            FROM
                '.TBL_FAQ.'
            WHERE
                IDFaq = "'.$IDFaq.'"
        ';
    }

    public static function getSqlInsertQuestion($IDUsuario, $ArDados) {
        return '
            INSERT INTO
                '.TBL_FAQ.'
                (IDUsuario, StTitulo, TxResposta)
            VALUES
                ("'.$IDUsuario.'", "'.$ArDados['StTitulo'].'", "'.$ArDados['TxResposta'].'")
        ';
    }

    public static function getSqlUpdateQuestion($IDUsuario, $ArDados) {
        return '
            UPDATE
                '.TBL_FAQ.'
            SET
                IDUsuario = "'.$IDUsuario.'", StTitulo = "'.$ArDados['StTitulo'].'", TxResposta = "'.$ArDados['TxResposta'].'"
            WHERE
                IDFaq = "'.$ArDados['IDFaq'].'"
        ';
    }

    public static function getSqlDeleteQuestion($IDFaq) {
        return '
            DELETE FROM
                '.TBL_FAQ.'
            WHERE
                IDFaq = "'.$IDFaq.'"
        ';
    }

    public static function getSqlFindCalledWaitingFor($IDUsuario, $ItLimit = false) {
        $ItLimit = $ItLimit ? 'LIMIT ' . $ItLimit : '';
        return '
            SELECT
                *
            FROM
                '.TBL_CALLED.'
            WHERE
                IDResponsavel = "'.$IDUsuario.'" AND EnStatus = "ATENDENTE"
        ' . $ItLimit;
    }

    public static function getSqlFindCalledByMessage($IDMensagem) {
        return '
            SELECT
                c.*
            FROM
                dra__Chamados c
            LEFT JOIN
                dra__HistoricoChamado hc
            ON
                hc.IDChamado = c.IDChamado
            WHERE
                hc.IDHistoricoChamado = "'.$IDMensagem.'"
        ';
    }

    public static function getSqlInsertExternalCalled($ArData) {
        return '
            INSERT INTO
                dra__Chamados
                (IDSetor, IDCliente, StTitulo, DtCriacao,
                EnTipoChamado, EnStatus, EnPrioridade)
            VALUES
                ("'.$ArData['IDSetor'].'", "'.$ArData['IDCliente'].'", "'.$ArData['StTitulo'].'", "'.$ArData['DtCriacao'].'",
                 "EXTERNO", "ATENDENTE", "FALSE")
        ';
    }

}

?>
